Can I use always INSERT ....ON DUPLICATE UPDATE for simple updates/inserts?

Question

(MYSQL) Is there any significant performance differences, or other reasons not to use the INSERT ... ON DUPLICATE UPDATE sql to update by PK/insert new stuff?

Answer 1

No, there is none.

INSERT ON DUPLICATE KEY UPDATE will locate the record and update it just as a simple UPDATE would do.

In fact this is just a UPDATE, followed by an INSERT should the UPDATE fail.

INSERT ON DUPLICATE KEY UPDATE can be a faster alternative to a grouped update (when you have a source table with multiple records per key and want to increment the target table once per record).

If there are few values per key, this query:

INSERT
INTO    t_target
SELECT  target, cnt
FROM    t_sparse d
ON DUPLICATE KEY UPDATE
        t_target.cnt = t_target.cnt + d.cnt

will be more efficient than this one:

INSERT
INTO    t_target
SELECT  target, cnt
FROM    (
        SELECT  target, SUM(cnt) AS cnt
        FROM    t_sparse di
        GROUP BY
                target
        ) d
ON DUPLICATE KEY UPDATE
        t_target.cnt = t_target.cnt + d.cnt

See this article in my blog for more detail:

• Efficient INSERT ON DUPLICATE KEY UPDATE