0

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

0

2 Answers 2

0

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.

7
  • Akina, I get the following error. Can you please advise? #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET PCLN.T1A = MIN(HST.Date) WHERE HST.SYM= PCLN.`In' at line 3
    – Quark
    Mar 25, 2019 at 8:22
  • I am sorry Akina, I do not understand the part about "no fragment shown". I used EXPLAIN and your query and I get the above error. Sorry for my ignorance.
    – Quark
    Mar 25, 2019 at 8:33
  • @Quark Misprint, edited. Check.
    – Akina
    Mar 25, 2019 at 8:34
  • I tried using EXPLAIN, the EXPLAIN query itself ran for over 300 sec after which I killed it. Next, when I tried using the actual query without EXPLAIN I get several errors "An Expression was expected near (", "Unexpected token" x3 and "A new statement was found, but no delimiter between it and the previous one". I use phpMyAdmin. Sorry for being a nag.
    – Quark
    Mar 25, 2019 at 8:50
  • @Quark The errors you show are not MySQL errors...
    – Akina
    Mar 25, 2019 at 9:22
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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