Menu
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?
953
The first step incurs a disk seek on large data sets with an ad-hoc primary (or unique key). And that is why it is slow. So, the moral of the story is this. In MySQL, “insert … on duplicate key update” is slower than “replace into”.
INSERT is faster than UPDATE because UPDATE does what INSERT does and before that it finds the record(s) and marks them deletion. I have a database with 500,000 records.
ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.
By definition, atomicity requires that each transaction is an all or nothing. So yes it is atomic in the sense that if the data that you are trying to insert will cause a duplicate in the primary key or in the unique index, the statement will instead perform an update and not error out.
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.
187
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
