Mysql – UPDATE vs INSERT INTO… ON DUPLICATE KEY UPDATE

indexMySQLupdate

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:
enter image description here

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:
enter image description here

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

Best Answer

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.

Related Question