MySQL: is it more efficient to query for duplicate row or insert(update) on duplicate?

Question

Just curious on resource efficiency. If I have a list of inserts that need to be accomplished repeatedly which may contain duplicates (there is no way around this, the API I have to work with sucks), is it better for me to query for the primary, and only insert if it doesn't exist (ie. 2 queries), or just insert anyway with update on duplicate syntax?

Thanks!

Answer 1

At 250/hour, all possible techniques are plenty fast. Anyway, I will throw my 2 cents in...

Multi-row INSERT of 100 rows will run 10 times as fast as 100 individual INSERTs.

Multi-row INSERT may lead to gaps in AUTO_INCREMENTs. In general, the command will preallocate all the ids it might need, then 'burn' the ones id did not use. (REPLACE burns lots, since it is DELETE + INSERT.)

For efficient "normalization" do something like:

INSERT IGNORE INTO HostNorm (host_name)
    SELECT DISTINCT s.host_name
        FROM Staging AS s
        LEFT JOIN HostNorm AS n  ON n.host_name = s.host_name
        WHERE n.host_id IS NULL;

• A list of possibly new values is in Staging.
• HostNorm is where 'new' rows need to be put.
• Only 'new' rows are fed to INSERT (see LEFT JOIN).
• IGNORE is to accommodate having multiple threads (connections) doing similar inserts that could collide. (A rare collision would lead to a burned id.)
• There is no cap on how many rows this can handle.
• This works best with, say, 100 rows.
• This is designed for very high speed input; it should work fine for your relatively slow ingestion rate.

More discussion in my blog.