5.5: UPDATE massively slower than SELECT. Both affect zero rows.
I'm seeing what seems like a ridiculous difference in performance with SELECT and UPDATE statements with identical JOINs and WHEREs. Since the UPDATE does not affect any rows I would not expect any sort of write to take significant time.
This query takes about a tenth of a second and returns zero rows.
SELECT *
FROM products AS p
INNER JOIN upcLike AS v ON v.upc=p.upc
INNER JOIN batchList as l ON l.upc=concat('LC',convert(v.likecode,char))
INNER JOIN batches AS b ON b.batchID=l.batchID
WHERE l.upc LIKE 'LC%'
AND l.batchID = 4895;
This query takes about three minutes and affects zero rows:
UPDATE products AS p
INNER JOIN upcLike AS v ON v.upc=p.upc
INNER JOIN batchList as l ON l.upc=concat('LC',convert(v.likecode,char))
INNER JOIN batches AS b ON b.batchID=l.batchID
SET p.modified = NOW()
WHERE l.upc LIKE 'LC%'
AND l.batchID = 4895;
Storage engine on all tables is InnoDB. The "upc" columns are VARCHARs. The "batchID" columns are INTs. The schema really isn't well designed, but there's got to be a pretty significant difference in the execution plan for the UPDATE to be thousands of times slower. Unfortunately, EXPLAIN doesn't work with UPDATE in 5.5 so I can't actually comparse the execution plans.
My instinct is it likely has to do with the ugly CONCAT+CONVERT join. Any insights into how execution plans potentially differ would be helpful.
Subject
Views
Written By
Posted
5.5: UPDATE massively slower than SELECT. Both affect zero rows.
1913
May 21, 2015 10:09AM
778
June 06, 2015 12:30PM
848
June 26, 2015 08:24AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.