MySQL Bulk-Insert Performance – Comparing Bulk-Insert Select vs Individual Inserts

bulk-insertMySQLperformance

I am using sequelize as ORM for my nodejs application.

The scenario is, I have to insert a Poll and its Options and get their objects.

Approach 1:

1 – Insert Poll:

INSERT INTO `polls` (`id`,`start_at`,`expire_at`,`options_count`) VALUES (DEFAULT,1487945658,1487945965,3);

2 – Insert Options (bulk insert):

INSERT INTO `poll_options` (`id`,`poll_id`,`text`) VALUES (NULL,{id},'red'),(NULL,{id},'green'),(NULL,{id},'blue');

3 – Select Options (I have to select because I don't get id of inserted Option due to bulk insertion):

SELECT `id`, `poll_id`, `text` FROM `poll_options` WHERE `poll_id` = {id};

Approach 2:

1 – Insert Poll

2 – Insert each Option one by one (this way I can get inserted id as well, so no need of explicit SELECT at the end)

Question:

Which approach would be better?

Both tables have referential integrity enabled and using innodb engine.

Best Answer

If we're talking performance, first approach is better. Here is why:

Whenever new data is inserted into a table, the indexes will also be updated, and physically reordered. In the case on InnoDB, not only are the index files updated, but the data file itself could be reordered, it is clustered based on the primary key. So, re-ordering/updating is an expensive operation, and you want to minimize it.

When you do bulk insert, the indexes are updated at when the statement is finished. i.e. after inserting all rows. On the other hand, when you do individual inserting, physical files are updated after each insert.

The overhead of the last select statement in the first approach is negligible.

Related Question