MySQL Forums
Forum List  »  Performance

5.5: UPDATE massively slower than SELECT. Both affect zero rows.
Posted by: Andy Theuninck
Date: May 21, 2015 10:09AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
5.5: UPDATE massively slower than SELECT. Both affect zero rows.
1913
May 21, 2015 10:09AM


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.