UPDATE vs INSERT INTO… ON DUPLICATE KEY UPDATE

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Method 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:

enter image description here

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply