CODEWITHSUNDEEP
mysqldatabaseperformanceoptimizationinsert
user3245050
user3245050

Reputation: 151

"Insert ignore" vs "select and insert"

I want to write a program add new item to table. This item has an unique key name and it can be created by one of 100 threads, so I need to make sure that it is inserted only once.

I have two ideas:

  1. Use insert ignore

  2. Fetch it from database via select then insert it to table if no returned row.

Which option is better? Is there an even more superior idea?

Upvotes: 15

Views: 11583

Answers (3)

Rick James
Rick James

Reputation: 142316

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

Upvotes: 12

Enyby
Enyby

Reputation: 4420

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.

Upvotes: 0

Woodgnome
Woodgnome

Reputation: 2391

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.

Upvotes: 17

Related Questions