1

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!

  • There is a 3rd option: INSERT ... SELECT ... WHERE NOT EXISTS (...); – ypercubeᵀᴹ Jul 30 '15 at 17:32
  • Would this be the most efficient option In terms of resources? – Chewy Jul 30 '15 at 17:34
  • Not sure. It will just avoid producing some gaps in the auto_increment column (if you have one.) I think the efficiency depends on how many duplicate values you expect. I suggest you do some testing with all options. My preference would on the ON DUPLICATE KEY or the NOT EXISTS version. – ypercubeᵀᴹ Jul 30 '15 at 17:54
  • 1
    How many rows per second do you expect to be inserting? If the volume is small, it may not be worth optimizing this. Just make something that works. – ypercubeᵀᴹ Jul 30 '15 at 17:55
  • Right now it will be 40 duplicates every hour. But there's no cap on that number, it it will go up over time. I don't expect it to EVER be > 1000 records, but the API I'm forced to use is sluggish as is, and am looking to save resources where I can. – Chewy Jul 31 '15 at 12:41
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.

  • This is fantastic and something I would have never come up with on my own. Thanks! – Chewy Aug 1 '15 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.