Optimize MySQL update statement

Question

I have an update query that selects data from a table (HST) and updates it into another table (PCL). The PCL table contains ~30000 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 PCL, it takes 3-4 hours, and in most cases I get "Lock wait timeout exceeded; try restarting transaction" errors and I have to keep trying until it works.

UPDATE PCL 
SET `T2A` = (SELECT HST.`Date` from `HST` WHERE HST.`SYM`=PCL.INST AND 
HST.`DATE` >= PCL.Date AND HST.`HP` >= PCL.T2 order by HST.`Date` limit 1) 
WHERE `BS` = 'B' AND `T1A` IS NOT NULL; 

Is there a way I can rewrite the above query so that it runs faster?

• ypercubeᵀᴹ: It would be better to add the output of SHOW CREATE TABLE name; (for both tables).
• ypercubeᵀᴹ: PCL has about 30K rows. How many are updated by this statement?
• Quark: Thanks ypercubeTM. I would say around 70%.

Answer 1

Think about something like

UPDATE PCL, ( SELECT PCL.SrNo, MIN(HST.Date) minDate
 FROM PCL, HST 
 WHERE HST.SYM=PCL.INST 
 AND HST.DATE >= PCL.Date 
 AND HST.HP >= PCL.T2 
 AND PCL.BS = 'B' 
 AND PCL.T1A IS NOT NULL
 GROUP BY PCL.SrNo ) subquery
SET PCL.T2A = subquery.minDate
WHERE PCL.SrNo = subquery.SrNo;

Of course, proper indices on both tables is safe.

• Quark: Thanks Akina, I should have mentioned that SrNo is primary key in each table but independent of one another.
• Akina: @Quark but independent of one another no dependence in my query. Only SrNo from PCL is used. Twice...
• Akina: @Quark Before running the whole query run the subquery separately and check, that all PCL.SrNo which must be updated are selected, and that proper Date is selected to each SrNo. If everything OK run the whole query. Then try to optimize the subquery itself by creating proper indices if needed - outer UPDATE do not need in optimization.
• Quark: Hi Akina, I tried running your query however I get a few syntax errors in phpMyAdmin. An expression was expected. (near (), Unexpected token. (near (), A new statement was found but no delimiter between it and the previous one. (near SELECT), Unexpected token. (near )), Unexpected token. (near subquery), A new statement was found but no delimiter between it and the previous one. (near SET). Can you please advise?
• Quark: Thank you very much Akina. I really appreciate your help.

Answer 2

Another method to write this kind of 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.T2
 ORDER BY hi.Date 
 LIMIT 1 
 ) 
SET p.T2A = h.Date 
WHERE p.BS = 'B'
 AND p.T1A IS NOT NULL ;

• Quark: Thanks ypercube. I cannot join on SrNo as they are not related.
• Akina: @Quark And again, you forget to look at tablenames where SrNo was taken from...
• ypercubeᵀᴹ: @Quark as Akina points, the join on SrNo is a self-join. Both come from HST.
• Quark: I am sorry. I am not very familiar with joins. Thank you very much for your advise.
• Quark: Thanks ypercubeTM. I get "Lock wait timeout exceeded; try restarting transaction" errors while trying to run your query. This appears to be a recurring problem on heavy queries. Is there something that can be done to avoid this error? I will appreciate your help.