On Duplicate Key Update - million times per day optimization? I am using the following query with different variables millions of times per day, and I am wondering if there is any way to optimize it:

    INSERT INTO campaign_stats (user_id, date, $country) 
    VALUES      (:user_id, :date, :country) 
    ON DUPLICATE KEY UPDATE $country=$country+1


The ON DUPLICATE KEY UPDATE $country=$country+1 is true in 99% of the time, so it might be better to check for it first?
The table is InnoDB, correct?  (If not, that change would be important.)

If there is an AUTO_INCREMENT column, you will burning most of a million values each day.  Please provideSHOW CREATE TABLE.

That's about 12/second -- not a deadly rate of changes.

Please time the IODKU and time the following, then report back:

UPDATE ... country=country+1;
if rows_affected = 0 then
INSERT ...,


I suspect they will be about the same speed, but I have not tested it.

Oops, I have a subtle bug in that code -- If another thread sneaks in and does the Insert while I am fumbling around with rows_affected, then the Insert could get "dup key".  The fix:  Change the INSERT to IODKU.

If you end up with more than 100/second, consider collecting the values, then patch applying them.  I would probably do a variant on http://mysql.rjweb.org/doc.php/staging_table .