"Insert ignore" vs "select and insert"

Late to the party, but I'm pondering something similar.

I created the following table to track active users on a license per day:

CREATE TABLE `license_active_users` ( `license_active_user_id` int(11) NOT NULL AUTO_INCREMENT, `license_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`license_active_user_id`), UNIQUE KEY `license_id` (`license_id`,`user_id`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

In other words, 1 primary key and 1 unique index across the remaining 3 columns.

I then inserted 1 million unique rows into the table.

Attempting to re-insert a subset (10,000 rows) of the same data yielded the following results:

• INSERT IGNORE: 38 seconds
• INSERT ... ON DUPLICATE KEY UPDATE: 40 seconds
• if (!rowExists("SELECT ...")) INSERT: <2 seconds

If those 10,000 rows aren't already present in the table:

• INSERT IGNORE: 34 seconds
• INSERT ... ON DUPLICATE KEY UPDATE: 41 seconds
• if (!rowExists("SELECT ...")) INSERT: 21 seconds

So the conclusion must be if (!rowExists("SELECT ...")) INSERT is fastest by far - at least for this particular table configuration.

The missing test is if (rowExists("SELECT ...")){ UPDATE } else { INSERT }, but I'll assume INSERT ... ON DUPLICATE KEY UPDATE is faster for this operation.

For your particular case, however, I would go with INSERT IGNORE because (as far as I'm aware) it's an atomic operation and that'll save you a lot of trouble when working with threads.

SELECT + INSERT -- two round trips to the server, hence slower.

INSERT IGNORE -- requires a PRIMARY or UNIQUE key to decide whether to toss the new INSERT. If this works for you, it is probably the best.

REPLACE -- is a DELETE + an INSERT. This is rarely the best.

INSERT ... ON DUPLICATE KEY UPDATE -- This lets you either INSERT (if the PRIMARY/UNIQUE key(s) are not found) or UPDATE. This is the one to use if you have things you need to update in existing rows.

"Burning ids" -- Only the "select+insert" avoids a potential problem: running out of AUTO_INCREMENT ids (I call it "burning ids"). All the other techniques will allocate the next id before deciding whether it is needed.

If you have several names to conditionally insert into a normalization, then a 2-query technique can batch them quite efficiently, and not burn ids: http://mysql.rjweb.org/doc.php/staging_table#normalization

Best: SELECT + INSERT IGNORE.

Because it is use SELECT for check it do not need lock table or row in table. Any INSERT need lock. So this can reduce performance on concurrent INSERT's.