Bulk-Insert Select performance over individual Inserts

Question

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.

Answer 1

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.

Answer 2

Although you're using an ORM, which hides the nitty gritty details of SQL for your developers, you seem to be interested in different approaches to insert a record and a multitude of detail records in a child table.

I would opt for the code, which conveys the intend most clearly and which can be maintained more easily. In my opinion, this will likely be the second option. Only if (measured) performance is an issue, then I would start optimizing.

Answer 3

But those are not the same?? Approach 1 retrieves one of the 3 ids; Approach 2 retrieves each of 3 ids.

And, why do you need any id from poll_options?

Seems like this is all you need:

BEGIN;

INSERT INTO `polls` (`start_at`,`expire_at`,`options_count`)
    VALUES (1487945658,1487945965,3);
-- note: no mention of polls.id, above.

{id} = LAST_INSERT_ID() -- however your language does this

INSERT INTO `poll_options` (`poll_id`,`text`)
     VALUES
     ({id},'red'),
     ({id},'green'),
     ({id},'blue');
-- again, no mention of poll_options.id

COMMIT;

Even so, the BEGIN and COMMIT are not really required. (At worst, you could might store a row in polls, then fail to store the rows in poll_options.

Performance:

• 1+1 inserts is nearly twice as fast as 1+3 inserts
• Getting LAST_INSERT_ID() is faster than any SELECT.