1

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'
  • Show us the output of SHOW CREATE TABLE tablename; for both tables – ypercubeᵀᴹ Mar 31 '19 at 19:12
  • @ypercubeᵀᴹ Thanks. Added SHOW CREATE TABLENAME; – Quark Apr 3 '19 at 8:43
1

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
;
|improve this answer|||||
  • Thanks @Kondybas. As you can tell, I am not really great with joins yet. My original update uses subqueries, it works well but is very slow. Can you please help rewrite this query preferably using joins so that it runs faster. I will appreciate your help. 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; – Quark Apr 2 '19 at 20:22
  • I agree with your comment about indexes, however for this specific query I am not too concerned with indexes for the WHERE conditions, as about 95% of the rows will satisfy p.BS = 'B' AND p.SrNo <18000; I am trying to see if I can replace the subquery with some kind of join and speedup the query. – Quark Apr 2 '19 at 20:26
  • @Quark You have to explain what is the meaning of the rows you want to fetch. – Kondybas Apr 3 '19 at 9:25
  • Thank you @Kondybas. I want to fetch the date when the value of HST.HP first exceeds or equals PCL.T1. Now the table PCL has some 17000 rows, the value of PCL.T1A needs to be set for all 17000 rows. I am selecting all HST.DATE >= PCL.Date so that all dates greater than or equal to PCL.Date are selected. Now I am applying MIN(HST.Date) to esnure that only the first date is selected. Initially, I tried below and the results are the same. SELECT HST.Date ... order by HST.Date limit 1. Additionally, WHERE HST.Sym=PCL.INS need to be applied. – Quark Apr 3 '19 at 10:12
  • @Quark I've added the query to my answer. – Kondybas Apr 3 '19 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.