Re: UPDATE .. WHERE or INSERT .. ON DUPLICATE KEY UPDATE?

[Rick James]

Short answer: Use UPDATE when appropriate.

Long answer:

When you say IODKU, you are implying that the record might not exist. Don't mislead the the next person reading your code -- it might be _you_ next year. If you know the row exists, or if you wish to get an error if it does not, then UPDATE makes more sense.

You probably found that the performance difference is insignificant. Furthermore, I suspect that there are flaws in the timing, since IODKU has to do more than a plain UPDATE:
1. Locate the row(s) by all PRIMARY and UNIQUE keys.
2. If none found, then perform INSERT.
3. Else perform UPDATE.

Furthermore, UPDATE picks a row based on the WHERE clause you give it, while IODKU picks a row(s) based on PRIMARY/DUPLICATE keys. That is, the two constructs are really asking for the "row" in different ways.

IODKU could have to touch more blocks -- because of the UNIQUE keys. If the table is huge and you are I/O-bound, this is likely to make it slower.

A common flaw in timing tests:
1. Run some query; it is slower.
2. Run another (or the same) query; it is faster.
The flaw: #1 possibly did I/O; #2 found the blocks already cached, so the I/O was not needed.

If you expect to be doing thousands of these IODKUs/UPDATEs per second, then let's talk further. Both techniques have scaling limits. More complex techniques are needed for reference counting in popular social media sites.