Performance issue of ON DUPLICATE KEY UPDATE in mysql bulk Update

Question

As Mysql doesn't Provide any Bulk Update query but we use the feature of ON DUPLICATE KEY UPDATE. Is it good to use the below query when we are updating in bulk if not then what are the performance issues of using the below query? Is there is any other way to bulk update in MySQL

INSERT into fruits(id, value) VALUES
         (1, 'apple'), (2, 'orange'), (3, 'peach'),
         (4, 'apple'), (5, 'orange'), (6, 'peach'),
         (7, 'apple'), (8, 'orange'), (9, 'peach'), (10, 'apple')
   ON DUPLICATE KEY UPDATE value = VALUES(value);

you say you are "confused" about performance in some way but offer no examples of that performance and how it is affected by your two examples. Also, it's difficult to see how anything meaningful could be measured using the examples you have used. Voting to close as "unclear what you are asking" — Vorsprung, Mar 27 '18 at 11:35
@Vorsprung I have changed the context of the question. But still, you can choose to close the question. — Anand Tripathi, Mar 27 '18 at 12:19

score 1 · Accepted Answer · answered Mar 29 '18 at 01:31

Clever trick. Let us know if it is faster than a 10 UPDATE statements. I suspect it is -- 9 fewer round trips to server; 9 fewer calls to parser; etc.

There is REPLACE, but that is very likely to be less efficient, since it is

DELETE all rows that match any UNIQUE index; and
INSERT the row(s) given.

IODKU is effectively

if row exists (based on any UNIQUE key)
    then do "update"
    else do "insert"

The effort to check if the row exists pulls the necessary blocks into cache, thereby priming things for the update or insert.

Performance issue of ON DUPLICATE KEY UPDATE in mysql bulk Update

1 Answers1