MySQL Forums
Forum List  »  Performance

Re: 5.5: UPDATE massively slower than SELECT. Both affect zero rows.
Posted by: Chandan Kumar
Date: June 26, 2015 08:24AM

Hi,
Update query will be slow then select ,the main reason is update will lock the all records from tables due to lack of indexes(isolation level repeatable read).
make sure you have index on joining fields.

you can try for better performance:

drop temporary table if exists temp_products ;
create temporary table temp_products
(KEY ix_temp_products(upc))
as
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;


UPDATE products AS p
JOIN temp_products X
USING (upc)
SET p.modified = NOW();

Assuming composite index (batchID ,upc ) on batchList


Thanks & Regards,
Chandan.
chandankumar9965@gmail.com

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: 5.5: UPDATE massively slower than SELECT. Both affect zero rows.
846
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.