bulkinsert - MySQL Insert 20K rows in single insert

Question

In my table I insert around 20,000 rows on each load. Right now I am doing it one-by-one. From mysql website I came to know inserting multiple rows with single insert query is faster.

Can I insert all 20000 in single query?

What will happen if there are errors within this 20000 rows? how will mysql handle that?

Answer 1

If you are inserting the rows from some other table then you can use the INSERT ... SELECT pattern to insert the rows.

However if you are inserting the values using INSERT ... VALUES pattern then you have the limit of max_allowed_packet.

Also from the docs:-

To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.

Example:-

INSERT INTO `table1` (`column1`, `column2`) VALUES ("d1", "d2"),
                                                 ("d1", "d2"),
                                                 ("d1", "d2"),
                                                 ("d1", "d2"),
                                                 ("d1", "d2");

What will happen if there are errors within this 20000 rows?

If there are errors while inserting the records then the operation will be aborted.