Index is not used when joining two tables in a stored procedure
Using where; Using join buffer (Block Nested Loop) is perhaps better than using an index.  It loaded the entire table in RAM so it can quickly probe it.

You seem to be simulating a FULL OUTER JOIN?  That can be made more efficient...


Create only one tmp table; then use a combination of the original and the tmp table.
Don't include the "intersection" in both parts of the UNION, thereby necessitating UNION DISTINCT (which is what you have), but rather the faster UNION ALL.
periodholdings LEFT JOIN tmp gives you the intersection and NULLs on one side
tmp LEFT JOIN periodholdings ... IS NULL gives you the other lobe of the Venn diagram.


Other issues...


If (stkcode,participantname) is unique, make it the PRIMARY KEY; that will speed things up.
Why have a name and id in the same table?  Shouldn't this be "normalized" into another table?
Don't blindly use (255); pick something smaller, but sufficiently big.  This will speed up any tmp tables needed.
decimal(5,4) - You are not going over 9.9999?


Please provide EXPLAIN FORMAT=JSON SELECT ...; it may provide some more details.