On Duplicate Key Update - million times per day optimization?
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 .