Combining two UPDATE Commands - Performance ?

Question

If I want to update two rows in a MySQL table, using the following two command:

UPDATE table SET Col = Value1 WHERE ID = ID1

UPDATE table SET Col = Value2 WHERE ID = ID2`

I usually combine them into one command, so that I do not to have to contact the MySQL server twice from my C client:

UPDATE table SET Col = IF( ID = ID1 , Value1 , Value2) WHERE ID=ID1 OR ID=ID2

Is this really a performance gain?

Background Information: I am using a custom made fully C written high-performance heavily loaded webserver.

Answer 1

Benchmark it. In theory it's faster, but it might be being optimized elsewhere without your knowledge (by the compiler or something else) and could make no difference in practice.

Answer 2

Whether or not that will perform better will depends on your setup. We can guess at it, but only profiling query times will tell you which is really better.

Answer 3

Yes, it will be faster simply because you make a single trip to the DB in instead of two. Likely the overhead of the net call will dominate this kind query (barring real bad optimizer decisions on MySQL's part).

However, even if you have two separate UPDATE statements, you can typically send them in a single request to the back end, and you'll stay with standard SQL also.

Answer 4

The single server request is typically a good idea since the round trips between client and server are often the most costly part of many queries (as Will Hartung already pointed out).

However, it might be useful to examine the optimization of that particular query. The condition id = id1 or id = id2 sounds like it will use a range search (see OR Relations). I'm not completely sure, but might result in an index scan from ID1 to ID2, which could be expensive if they are logically "far" apart in the index. It may be more efficient to use the IN operator: where ID in (ID1, ID2). Or possibly use two individual statements.