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.
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
COLUMNS TERMINATED BY ','
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
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.
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.