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:


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://...'
    INTO TABLE our_table
    (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). Jun 30, 2018 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. Jun 30, 2018 at 19:39
  • OK. You can add that the same issue appears both with INSERT and LOAD then. Thnx for the interesting question! Jun 30, 2018 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. Jul 1, 2018 at 6:15
  • "Insert Ignore" means "insert… on duplicate key do nothing" Jul 1, 2018 at 6:16

1 Answer 1


As one can see from the source code, when a row is inserted into an InnoDB table, it gets added to the clustered index first, then to all secondary indexes in turn. In your case the clustered index is based on an auto-increment column, so the first operation always succeeds. The next operation on a secondary unique index fails because of the duplicate key value. The engine then proceeds to roll back the previous operation, incurring unnecessary performance penalty.

The logic is clearly optimised for the "happy path" -- non-conflicting inserts, at the cost of suboptimal exception path. If they were to validate constraints before applying any changes, conflict resolution cost would be reduced, but the "happy path" would become more expensive, making more users unhappy.

Yours being an AWS Aurora instance, the problem is likely made even worse by the fact that both changes need to be written to the quorum of change logs for replication to the secondary node(s), as well as its possible negative effects on the fast insert acceleration.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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