CODEWITHSUNDEEP
mysqloptimization
johnnietheblack
johnnietheblack

Reputation: 13320

Which performs better in MySQL?

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?

Upvotes: 0

Views: 58

Answers (2)

Starx
Starx

Reputation: 78971

Single query will be far more faster than querying the server multiple times in a loop.

So, second way is the way to go.

Upvotes: 2

Daan
Daan

Reputation: 3348

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 :)

Upvotes: 1

Related Questions