0

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?

  • 2
    It would be better to add the output of SHOW CREATE TABLE name; (for both tables). – ypercubeᵀᴹ Feb 22 at 7:55
  • PCL has about 30K rows. How many are updated by this statement? – ypercubeᵀᴹ Feb 22 at 8:24
  • Thanks ypercubeᵀᴹ. I would say around 70%. – Quark Feb 22 at 8:38
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.

  • Thanks Akina, I should have mentioned that SrNo is primary key in each table but independent of one another. – Quark Feb 22 at 8:42
  • 1
    @Quark but independent of one another no dependence in my query. Only SrNo from PCL is used. Twice... – Akina Feb 22 at 8:44
  • @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. – Akina Feb 22 at 8:51
  • 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 Feb 22 at 21:22
  • Thank you very much Akina. I really appreciate your help. – Quark Feb 25 at 12:33
0

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 ;
  • Thanks ypercube. I cannot join on SrNo as they are not related. – Quark Feb 22 at 9:03
  • @Quark And again, you forget to look at tablenames where SrNo was taken from... – Akina Feb 22 at 11:30
  • @Quark as Akina points, the join on SrNo is a self-join. Both come from HST. – ypercubeᵀᴹ Feb 22 at 11:35
  • I am sorry. I am not very familiar with joins. Thank you very much for your advise. – Quark Feb 22 at 11:48
  • Thanks ypercubeᵀᴹ. 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. – Quark Feb 22 at 21:26

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.