Which performs better in MySQL?
Question by johnnietheblack
I would like to update 3 rows of a single table, with different values each.
The way I see it I could either…
Option 1.
Simply create a PHP loop, and run a query for each row I’d like to update. (this is the ‘classic’ approach, maybe).
Option 2.
Follow the approach of combining into a single query, but that uses syntax that I have heard is slower, depending on the situation. (example copied from: this website):
UPDATE mytable
SET myfield = CASE other_field
WHEN 1 THEN 'value'
WHEN 2 THEN 'value'
WHEN 3 THEN 'value'
END WHERE id IN (1,2,3)
Which is a better, faster option?
Answer by Daan
I’m almost certain the single MySQL query will be slower than separate UPDATE queries, but you could always try. If immediate consistency is not a concern, you could also consider using individual UPDATE LOW_PRIORITY queries which should be even faster still.
The best way to know what works for your application, as always in programming, is to benchmark the performance of both queries and experiment a bit 🙂
Answer by Starx
Single query will be far more faster than querying the server multiple times in a loop.
So, second way is the way to go.
