MySQL Update multiple rows VS Insert update on duplicate key, Which one is faster?

Posted on

Question :

In MySQL, I can update multiple rows in two ways, I think so.

  1. Calling update query several times to update rows.
  2. Calling one Insert query with On Depulicated Key Update.

Which one is faster?
Let’s assume that we are handling a big database and we are updating lots of rows.


Answer :

Please provide an example of multiple updates vs one IODKU. There could be subtle issues that have a big impact on the comparison. IN(...) comes to mind.

In general, there is “a lot” of overhead per statement. That says 1 query is likely to beat several queries.

A specific example: 100 single-row INSERTs takes about 10 times as long as one INSERT with 100 rows in it.

Check to see if the queries are inside a transaction, versus simply run with autocommit=ON. If not already in a transaction, you can gain some speed by sticking BEGIN and COMMIT around the batch of write commands. (With simply autocommit=ON, each write is effectively a transaction. The above replaces the overhead for many transactions into one.)

Leave a Reply

Your email address will not be published. Required fields are marked *