Mysql – way to speed up this update query using joins or any other method

MySQLmysql-5.6performancequery-performanceupdate

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

Best Answer

Look for

UPDATE pcln, (  SELECT pcln.srno, MIN(hst.`date`) mindate
                FROM pcln, hst
                WHERE hst.sym=pcln.ins 
                  AND hst.`date` >=  pcln.`date` 
                  AND hst.hp >= pcln.t1
                  AND pcln.bs = 'B'
                GROUP BY pcln.srno  ) tmp
SET pcln.t1a = tmp.mindate
WHERE pcln.srno = tmp.srno;

To optimize the subquery create index pcln(bs,ins). Or the index which is covering for the subquery.