MySQL – Creating sort index and update query takes forever

join;MySQLupdate

I have the following MySQL query 

UPDATE PCL AS p
JOIN HST AS h
ON h.SrNo = 
   ( SELECT hi.SrNo
     FROM HST AS hi
     WHERE hi.SYM = p.INST
       AND hi.DATE >=  p.Date 
       AND hi.HP >= p.T1
     ORDER BY hi.Date 
     LIMIT 1 
   )    
SET p.T1A = h.Date 
WHERE p.BS = 'B'
AND p.SrNo <18000;

However, this query takes over 3 hours to execute, SHOW FULL PROCESSLIST shows "Creating sort index" and almost 95% of the time it is creating sort index.

EXPLAIN SHOWS

Select type: DEPENDENT SUBQUERY
table: hi
type: index
possible keys: NULL
key: hst_idx_sym_date_hp
key_len: 32
ref: NULL
rows: 1002718
extra: Using where; Using index; Using filesort

I also tried the below update queries using subquery, but they are even slower

UPDATE PCL 
SET `T1A` = (SELECT HST.`Date` from `HST` WHERE HST.`SYM`=PCL.INST AND 
HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.T1 order by HST.`Date` limit 1)    
WHERE `BS` = 'B' AND SrNo <18000; 

UPDATE PCL 
SET `T1A` = (SELECT MIN(HST.`Date`) FROM `HST` WHERE HST.`Sym`=PCL.INST AND 
HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.`T1`)    
WHERE `BS` = 'B' AND SrNo <18000;

Is there a way to optimize this query so that it executes faster?

Here is the SHOW CREATE TABLEs

CREATE TABLE `PCL` (
`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'

Best Answer

You have joined HST on PCL but ON condition refers to the HST itself and there is no relation between HST and PCL. The table PCL is fully scanned and the only restriction used is the WHERE p.BS = 'B' AND p.SrNo <18000 that has no corresponding index.

UPDATE

I want to fetch the date when the value of HST.HP first exceeds or equals PCL.T1. 

UPDATE PCL AS pp
  JOIN ( SELECT h.Date AS date_the_value_first_exceeded
           FROM PCL AS p
           JOIN HST AS h  ON h.Date = p.Date      -- Joining tables by dates
          WHERE h.HP >= p.T1                      -- The condition you want
          ORDER BY p.Date ASC                     -- Order the result chronologically 
          LIMIT 1                                 -- Single line required
       ) AS dd                                    -- Unrestricted join, each to each 
   SET pp.T1A = dd.date_the_value_first_exceeded
 WHERE pp.BS = 'B'
   AND pp.SrNo < 18000
;

The version with MIN() function:

UPDATE PCL AS pp
  JOIN ( SELECT MIN(h.Date) AS date_the_value_first_exceeded
           FROM PCL AS p
           JOIN HST AS h  ON h.Date = p.Date      -- Joining tables by dates
          WHERE h.HP >= p.T1                      -- The condition you want
       ) AS dd                                    -- Unrestricted join, each to each 
   SET pp.T1A = dd.date_the_value_first_exceeded
 WHERE pp.BS = 'B'
   AND pp.SrNo < 18000
;
Related Question