MySQL Forums
Forum List  »  Performance

Re: Update Performance Problem
Posted by: Øystein Grøvlen
Date: November 05, 2013 02:00AM

Hi Peter,

Which version of MySQL are you using?
If you are using MySQL 5.6 you will be able to run EXPLAIN on your UPDATE statement to get the plan for execution. That would make it easier to identify your problem.

MySQL 5.6 contains improvements to execution of subqueries, but unfortunately these improvements do not apply to UPDATE statements. Hence, in your case, I think the best thing would be to first create a temporary table with the rows to be updated and then run the update statement on those rows. That is, for your first statement, first do:

CREATE TEMPORARY TABLE temp (id INTEGER PRIMARY KEY) AS
SELECT DISTINCT pb.id FROM pbmc.polymotif pm
JOIN pbmc.beatinmotif bim ON pm.id=bim.motif_id
JOIN pbmc.polybeat pb ON pb.id=bim.beat_id
JOIN pbmc.noteinbeat nib ON nib.polybeat_id=pb.id
JOIN pbmc.polynote pn ON pn.id=nib.polynote_id
WHERE pm.category_id = 1 AND pn.pitch IN
(SELECT na.pitch
FROM pbmc.note_amount na
WHERE na.category_id = pm.category_id AND na.amount BETWEEN 5 AND 10);

and then:

UPDATE pbmc.polybeat pbb SET pbb.interesting_measure = 1 WHERE pbb.id IN
(SELECT id from temp);

Hope this helps,

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway

Options: ReplyQuote


Subject
Views
Written By
Posted
2713
November 04, 2013 06:20PM
Re: Update Performance Problem
929
November 05, 2013 02:00AM
826
November 05, 2013 11:32AM


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.