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;

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);