Index is not used when joining two tables in a stored procedure In MySQL 5.7, I have two tables:

CREATE TABLE `periodholdings ` (
   `stkcode` varchar(255) NOT NULL,
   `participantid` varchar(255) DEFAULT NULL,
   `participantname` varchar(255) DEFAULT NULL,
   `holdingdate` date DEFAULT NULL,
   `shares` bigint(20) DEFAULT NULL,
   `percentage` decimal(5,4) DEFAULT NULL,
   KEY `sp` (`stkcode`,`participantname`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `pholdings` (
   `stkcode` varchar(255) NOT NULL,
   `participantid` varchar(255) DEFAULT NULL,
   `participantname` varchar(255) DEFAULT NULL,
   `holdingdate` date DEFAULT NULL,
   `shares` bigint(20) DEFAULT NULL,
   `percentage` decimal(5,4) DEFAULT NULL,
   KEY `sp` (`stkcode`,`participantname`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `choldings` (
   `stkcode` varchar(255) NOT NULL,
   `participantid` varchar(255) DEFAULT NULL,
   `participantname` varchar(255) DEFAULT NULL,
   `holdingdate` date DEFAULT NULL,
   `shares` bigint(20) DEFAULT NULL,
   `percentage` decimal(5,4) DEFAULT NULL,
   KEY `sp` (`stkcode`,`participantname`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


In the SP, I called the following:

    drop table if exists pholdings;
    create table pholdings (index sp(stkcode, participantname)) as select * from periodholdings where holdingdate = '2016-09-01'; 

    drop table if exists choldings;
    create table choldings (index sp(stkcode, participantname)) as select * from periodholdings where holdingdate = '2016-09-21'; 

   explain
    (select p1.stkcode, p1.participantid, p1.participantname, if(c1.shares is null, -p1.shares/100, (c1.shares - p1.shares)/100) as averagediff from pholdings p1 use index(sp) left join choldings c1 use index(sp) on p1.stkcode = c1.stkcode and p1.participantname = c1.participantname)
    union
    (select c1.stkcode, c1.participantid, c1.participantname, if(p1.shares is null, c1.shares/100, (c1.shares - p1.shares)/100) as averagediff from pholdings p1 use index(sp) right join choldings c1 use index(sp) on c1.stkcode = p1.stkcode and c1.participantname = p1.participantname);


However, the index is not used at all.

1   PRIMARY p1      ALL                 407340  100.00  
1   PRIMARY c1      ALL sp              412439  100.00  Using where; Using join buffer (Block Nested Loop)
2   UNION   c1      ALL                 412439  100.00  
2   UNION   p1      ref sp  sp  1535    ccass.c1.stkcode,ccass.c1.participantname   70012   100.00  
    UNION RESULT    &lt;union1,2&gt;      ALL                         Using temporary


If I just put the last EXPLAIN statement into the query and modify a little (like exchange the c1 and p1 around the =), the index works again.
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.