2

Let me explain :

Let us assume we have a database "user" :

ID   Name   Surname
===================
1    John   Doe
2    Foo    Bar
3    Foo    Baz

Now I wanna change all these three rows. I will throw the following insert :

INSERT INTO user (ID, Name, Surname) VALUES (1, 'John', 'Conor') ON DUPLICATE KEY UPDATE ID = 1, Name = 'John', Surname = 'Conor';
INSERT INTO user (ID, Name, Surname) VALUES (2, 'Foo', 'Bar') ON DUPLICATE KEY UPDATE ID = 2, Name = 'Foo', Surname = 'Bar';
INSERT INTO user (ID, Name, Surname) VALUES (3, 'Foo', 'Baz') ON DUPLICATE KEY UPDATE ID = 3, Name = 'Foo', Surname = 'Baz';

Question

Does the command ON DUPLICATE KEY execute itself if the row before the update was the same ? Meaning, does it will execute for the rows with ID equal to 2 and 3 even if it should not executes (because it is useless) ? I ask this question for performance issues.

6

MySQL executes ON DUPLICATE KEY UPDATE the same way it executes UPDATE statements:

It checks the contents of each row (and columns) to be updated and if they are identical to the supplied, it does not do any update. It still has to check them though.

So the result in your case (where you send 3 rows to be inserted) would be:

  • 3 rows to be inserted
  • 3 key collisions
    • 0 rows inserted
    • 3 to be updated
      • 1 row actually updated
      • 2 identical (no update)

A few things about your syntax:

  • you don't need to update the UNIQUE key.
  • you can use VALUES(column) in the UPDATE part.
  • you can combine the multiple inserts into one:

    INSERT INTO user 
      (ID, Name, Surname) 
    VALUES 
      (1, 'John', 'Conor'),
      (2, 'Foo',  'Bar'),
      (3, 'Foo',  'Baz') 
    ON DUPLICATE KEY UPDATE 
      Name = VALUES(Name), 
      Surname = VALUES(Surname) ;
    
  • Awesome shortcuts that I will use for sure ! Thank you for your answer, this also answers the performance issue (I was afraid if, one one millions of rows, it throws the UPDATE on duplicate, some times I have cases when the database has 1 millions rows, and only 5% are to be updated, and 10-15% of new rows). – Anwar Feb 20 '17 at 16:19
  • Even 5% of a million rows is a lot; there may be contention issues if other connections are trying to get at the table. – Rick James Feb 20 '17 at 16:35

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.