MySQL Forums
Forum List  »  Performance

Re: 5.5: UPDATE massively slower than SELECT. Both affect zero rows.
Posted by: Rick James
Date: June 06, 2015 12:30PM

UPDATE and SELECT sometimes use different optimizers; I don't know if this is such a case.

This might take advantage of SELECT's speed:
UPDATE products AS p
JOIN
( SELECT v.upc
FROM upcLike AS v
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
) AS x USING (upc)
SET p.modified = NOW();

`batchList` needs a composite index:
INDEX(batchID, upc)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: 5.5: UPDATE massively slower than SELECT. Both affect zero rows.
777
June 06, 2015 12:30PM


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.