MySQL: is it more efficient to query for duplicate row or insert(update) on duplicate? 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!
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.