Is there a way to speed up this update query using joins or any other method

Question

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

Answer 1

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.

Answer 2

Possible minor optimization...

UNIQUE KEY `HST_idx_SYM_Date`    (`SYM`,`Date`)
UNIQUE KEY `HST_idx_SYM_date_HP` (`SYM`,`Date`,`HP`)

The uniqueness of the pair of columns necessarily indicates that the 3-column combo will be UNIQUE. Changing the second one to INDEX would speed up INSERTs/UPDATEs/DELETEs.

At that point, there is virtually no need for such 3-column INDEXes. So, keep only such indexes that are "covering", as indicated by "Using index" in EXPLAIN.

When there are multiple 'ranges' in a WHERE clause (as in your original query), only one of the ranges will be useful. That is, an index on (SYM,DATE) is just as good as (SYM,DATE, HP) since the Optimizer can't make use of both ranges on DATE and HP.