CODEWITHSUNDEEP
mysql
antak
antak

Reputation: 20749

Performance of "INSERT ... ON DUPLICATE KEY UPDATE" compared to "UPDATE"

99% of the time a single UPDATE will suffice, but less than 0.01% of the time I need to follow up with an INSERT on the same key. This can be written as a single INSERT ... ON DUPLICATE KEY UPDATE but, in terms of performance, should I?

The question is basically if INSERT ... ON DUPLICATE KEY UPDATEs are expected to be slower than UPDATEs in MySQL.

Some code to demonstrate:

Code using UPDATE

UPDATE foo SET seq = @new_seq WHERE id = X AND seq = @prev_seq;

IF rows_changed = 0 THEN
    INSERT INTO foo (id, seq) VALUES (X, @new_seq)
    IF <duplicate key error> THEN
        -- handle collusion
    END IF
END IF

Code using INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO foo (id, seq) VALUES (X, @new_seq)
    ON DUPLICATE KEY UPDATE seq = IF(seq = @prev_seq, VALUES(seq), seq)

IF rows_changed = 0 THEN
    -- handle collusion
END IF

Upvotes: 4

Views: 5036

Answers (1)

walter
walter

Reputation: 1239

It is strongly recommended that you benchmark them by yourself.

It's quite easy to do, and it's more persuasive.

In theory, I don't think that the INSERT ... ON DUPLICATE KEY UPDATE will drag you down.

CASE 1: update table without index

In this case, the UPDATE operation will lock all records in this table. But INSERT operation only locks a gap(Insert Intention Lock) and the inserted record.

Since you used INSERT ... ON DUPLICATE KEY UPDATE here, it's more likely that you are updating table with index.

CASE 2: update table with index

Let's assume that you are updating table with a primary key. Both UPDATE and INSERT will pay the same cost to find the specific record in a B+ tree. And it's the same for INSERT ... ON DUPLICATE KEY UPDATE. So there is no reason INSERT ... ON DUPLICATE KEY UPDATE would be much slower.

What's more, INSERT ... ON DUPLICATE KEY UPDATE won't add a Insert Intention Lock if a duplicate key is found. It will only add a IX lock on the table and a X lock on the specific record, just as the UPDATE do.

Upvotes: 6

Related Questions