3

TL;DR: Inserting a large number of rows with no collisions is MUCH faster than (not) re-inserting the same rows, BOTH using the INSERT IGNORE syntax.

Why is this? I would assume that the index lookup cost would be the same for an insert and an "ignored" insert, given that MySQL does not know if the incoming data has repeated/conflicting data (and therefore needs to be ignored)...thus, indexing occurs in both the initial insert and the ignored insert runs.

Furthermore, I would assume that an "ignored" row should be CHEAPER in that it does not require any disk writes.

But this is most definitely NOT the case.

Long Version: In this question, we use AWS's Aurora/MySQL and the LOAD DATA FROM S3 FILE syntax to remove any transfer or performance variables. We load a 4 megarow CSV file corresponding to the schema below, and load it in twice, both times with LOAD ... IGNORE.

Please note that the issue also occurred with standard INSERT ... IGNORE, but with use of batched row inserts. The use of LOAD ... IGNORE here is to steer the discussion towards the counter-intuitive nature of the measured results, and not "how to perform large number of ignored inserts". That is not the issue here, as a domain-specific method was worked out.

In the model being tested, there is a three-tier index: the first two being enumerable categorization columns with very low cardinality, and a third column that is essentially "actual" data. For ease of qualifying this question, I am just sticking to my current setup.

Assume the following trivial schema:

our_table:

id: basic auto-increment bigint, primary key
constkey1: varchar(50) -- this is a constant in the insert
constkey2: varchar(50) -- this is a constant in the insert
datakey:   varchar(50) -- this is pulled in from the CSV file
datafield: varchar(50) -- this is pulled in from the CSV file
consttime: datetime    -- this is a constant in the insert

Unique Index: (constkey1, constkey2, datakey)

And the following query, run twice:

LOAD DATA FROM S3 FILE  's3://...'
    IGNORE
    INTO TABLE our_table
    COLUMNS TERMINATED BY ','
    (datakey, datafield)
    SET constkey1 = 'some string',
        constkey2 = 'some other string',
        consttime = CURRENT_TIMESTAMP  -- so that I can verify changes

Also assume that our_table already contains several million rows, NONE of which have have NEITHER the same constkey1 NOR constkey2 as that given in the query.

  • The first time I run the query above, the 4 megarows take 3 to 5 minutes to insert (presumably based on the vagrancies of the current database environment at the time of the run), and all 4 megarows are properly inserted. This is an acceptable baseline.

  • The SECOND time I run the query, the 4 megarows take an insane amount of time to (not) insert, and as expected, all 4 megarows are properly NOT updated. The time is long enough to not even be worth qualifying here (one run nearly an hour).

This test was run a number of times, always yielding the same result.

UPDATE: While modifying this question, the following question was suggested by S.E.

mysql insert into indexed table taking long time after few million records

While I do not think it completely generic here, perhaps it has something to do with memory size of the index range? In the example here, the "sub index" size of ('some string', 'some other string', * ) was ZERO in run one, while full of 4 megaentries in run 2. Perhaps That is an issue?

Please also note that the eventual solution was of the form given in the question below. This is immaterial to the question at hand, as I wish to know why the original solution did not work. This is merely given for those curious.

Can I identify duplicates in a large MySQL insert?

  • Why does the title and your question start with "INSERT-IGNORE", while the code shows "LOAD-IGNORE"? INSERT is not the same as LOAD (although similar). – ypercubeᵀᴹ Jun 30 '18 at 19:27
  • Thank you for the pointer about LOAD versus INSERT. The problem is indeed with insert: the original method was to actually use batches of INSERTS, and the same problem occurred. The use of LOAD here was to direct people away from the issue of inserting a large number of lines. The issue is not the large number lines per se, but rather that that the mechanism appears to be the cause of the problem and not the size. – Mark Gerolimatos Jun 30 '18 at 19:39
  • OK. You can add that the same issue appears both with INSERT and LOAD then. Thnx for the interesting question! – ypercubeᵀᴹ Jun 30 '18 at 19:41
  • 1
    Are you sure that it actually check for duplicates before trying to insert? If it does the insert and catches the exception it could explain the extra time. I have no idea if thats the case, it's just a thought. – Lennart Jul 1 '18 at 6:15
  • "Insert Ignore" means "insert… on duplicate key do nothing" – Mark Gerolimatos Jul 1 '18 at 6:16

Your Answer

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

Browse other questions tagged or ask your own question.