Performance question: ON DUPLICATE KEY UPDATE vs UPDATE (MySQL)

Question

Is there performance difference between INSERT INTO ON DUPLICATE KEY UPDATE and UPDATE?

If I know the values that can be UPDATEd - should I use UPDATE or it does not really matter?

Answer 1

There is a difference.

The INSERT query has to check the constraints on every column to see if they're violated by adding that row. If so, it then needs to find the matching row to update and perform the update.

An UPDATE query only has to find the row to update and perform the update.

If you know the row already exists, you should just UPDATE it.

Answer 2

Consider the following:

• UPDATE ... SET ...

UPDATE ... SET ... WHERE <condition when to update>

It means that the database engine will go through the whole table to update all record matching the conditions. If you don't specify a condition, it will perform the update for every record in the table.

• INSERT INTO ... ON DUPLICATE KEY UPDATE ...

INSERT INTO ... ON DUPLICATE KEY UPDATE ...

The database engine will try to insert the new record, and if it finds that the key you provided already exists, it will only update that record, and that is all. Key columns are indexed, so it will find out almost immediately if the key already exists.

Read this answer for more details on KEY columns.

So if you need to update only one record, it can be done faster using INSERT INTO ... ON DUPLICATE KEY ...