I have an update query that selects data from a table (HST) and updates it into another table (PCLN). The PCLN table contains ~17000 rows while the HST table contains about 1.1 million rows. I have multiple (composite) indexes on HST and despite being a large table all queries on it are fairly quick (~2-3 sec). However, when I attempt to select rows from this table and update PCLN, it takes 3-4 hours.
UPDATE PCLN SET `T1A` = ( SELECT MIN(HST.`Date`) FROM `HST` WHERE HST.`SYM`=PCLN.INS AND HST.`DATE` >= PCLN.Date AND HST.`HP` >= PCLN.`T1`) WHERE `BS` = 'B';
I had asked a similar question before and did see some benefits after implementing the advise that was given, however the query is still very slow and takes hours to complete. I am not very good with MySQL yet but feel that there should be some way to improve the performance of this update query.
Here is the SHOW CREATE TABLE PCLN;
CREATE TABLE `PCLN` ( `SrNo` int(6) NOT NULL AUTO_INCREMENT, `Date` date DEFAULT NULL, `INS` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, `T1` float(8,2) DEFAULT NULL, `T1A` date DEFAULT NULL, ...... PRIMARY KEY (`SrNo`) ) ENGINE=InnoDB AUTO_INCREMENT=16893 DEFAULT CHARSET=latin1
and SHOW CREATE TABLE HST
CREATE TABLE `HST` ( `SrNo` int(11) NOT NULL AUTO_INCREMENT, `SYM` varchar(20) DEFAULT NULL, `Date` date DEFAULT NULL COMMENT 'YYYY-MM-DD', `HP` float(8,2) DEFAULT NULL, .... PRIMARY KEY (`SrNo`), UNIQUE KEY `HST_idx_SYM_date_OP` (`SYM`,`Date`,`OP`), UNIQUE KEY `HST_idx_SYM_date_CP` (`SYM`,`Date`,`CP`), UNIQUE KEY `HST_idx_SYM_date_PC` (`SYM`,`Date`,`PC`), UNIQUE KEY `HST_idx_SYM_date_LP` (`SYM`,`Date`,`LP`), UNIQUE KEY `HST_idx_SYM_Date` (`SYM`,`Date`) USING BTREE, UNIQUE KEY `HST_idx_SYM_date_HP` (`SYM`,`Date`,`HP`) USING BTREE, KEY `HST_idx_date` (`Date`) ) ENGINE=InnoDB AUTO_INCREMENT=1103583 DEFAULT CHARSET=latin1 COMMENT='HST Data - All'
On EXPLAIN I noticed that for the index HST_idx_date, it says 1002718 Range checked for each record (index map: 0x80). What does this signify, is there a way to limit the rows so that the query is sped up. Any suggestions to speed up the query would be greatly appreciated.
Server version: 5.6.39-cll-lve - MySQL Community Server (GPL).