UPDATE vs INSERT INTO... ON DUPLICATE KEY UPDATE

Question

Alright so I have this table in which until now I was using the following queries to UPDATE else do an INSERT:

$db->query("UPDATE ulogs SET invalid = invalid + 1 WHERE uid = 666 AND date = '2018-04-18'");
if ($db->affectedRows() < 1) {
    $db->query("INSERT INTO ulogs (uid,date,invalid,unique,nonu,ea,ref,bo) VALUES (666,'2018-04-18',1,0,0,0,0,0)";
}

I had a regular combined index (non-unique) on uid&date columns. The update query was using the INDEX. All is fine but I was wondering if switching to ON DUPLICATE KEY UPDATE would be better, especially as the two columns' INDEX can be switched to UNIQUE. So I went ahead and modified the INDEX to an UNIQUE one, then rewrote the query as follows:

$db->query("INSERT INTO ulogs (uid,date,invalid,unique,nonu,ea,ref,bo) VALUES (666,'2018-04-18',1,0,0,0,0,0) ON DUPLICATE KEY UPDATE invalid = invalid + 1";

However, mysql explain is showing that the INDEX is not used in this case. Also for some reason it shows the query as an INSERT operation, even though an UPDATE is performed:

Also, by running the query in phpmyadmin, for some reason it shows that 2 rows have been INSERTed, while in fact only 1 row was UPDATEd with the data:

Bottom line is, which operation would be the fastest in terms of code optimization? Thank you.

Answer 1

The fastest operation will be the one that has the most chances of succeeding immediately. You should find out what happens more often: inserting new rows or updating old ones. Code accordingly.

Answer 2

So I decided to TEST both methods in my application and using the actual database. In the performed test, I'll refer to "METHOD 1" as the one in which we check for affected rows and insert if necessary, and subsequently refer to "METHOD 2" as using INSERT INTO.. ON DUPLICATE KEY UPDATE. Both methods have been ran on actual data 20 times with at least 5 seconds gap between tests:

So as to answer my own doubt, at least in case of UPDATES, using the 1st method is the winner, though the 2nd one isn't bad either.