0

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.

  • 2
    From a manual page I found somewhere: "With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. " – Gerard H. Pille Apr 18 '18 at 8:34
  • Oh, that explains it then. Thanks a lot for clearing that up! – Ivan Apr 18 '18 at 8:35
  • If there are multiple threads and uid / date is unique, there could be a risk that the INSERTs could collide. You can avoid this by putting the UPDATE and INSERT in the same transaction. – dbdemon Apr 18 '18 at 13:12
  • Interesting point which adds another reason to rather use INSERT INTO ON DUPLICATE KEY UPDATE instead as that would eliminate the risk of colliding inserts. Am I right? – Ivan Apr 18 '18 at 15:49
  • The INSERT ... ON DUPLICATE KEY UPDATE is a single transaction already, so that is definitely easier to program. The alternative would be maybe a stored procedure with something along the lines of START TRANSACTION ... UPDATE ... IF something THEN INSERT ... COMMIT; I'm not sure if the exact syntax off the top of my head, but it's definitely more complicated. On the other hand it might be slightly faster. So it depends: do you prefer code simplicity or performance? – dbdemon Apr 18 '18 at 16:29
2

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.

  • In that case, the most often operations are the UPDATEs, however in the first case, there is that little overhead of an extra db call, if any, of checking if an UPDATE has actually happened, which is what I'd like to remove. On the other hand, it's the reverse of checking for an INSERT else UPDATE which would be true all the times, but with the advantage of only 1 db query. Still can't decide... – Ivan Apr 18 '18 at 8:41
  • You wouldn't happen to have a coin with heads on both sides? – Gerard H. Pille Apr 18 '18 at 8:43
  • BTW, in stead of worrying about an extra db call, I'd worry about not using bind variables and prepared statements. – Gerard H. Pille Apr 18 '18 at 8:45
  • I'm using prepared statements already. The code here is just an example with actual values for understanding context. – Ivan Apr 18 '18 at 8:46
  • A real pity, there's really no need for more bad examples. – Gerard H. Pille Apr 18 '18 at 8:49
1

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.

  • 1
    I'd try with much more load, say 20K inserts. Doing just 20 rows isn't very accurate. The diff 0f 7% is only 0.03 milliseconds. – ypercubeᵀᴹ Apr 18 '18 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.