Is batch SQL insert good way for mySQL optimization?

Question

I would like to insert more rows in one time into my SQL table. I would like to find out what is the best way to do it.

1.) use INSERT statement for each piece of data

INSERT INTO message (email, txt) VALUES ('[email protected]', 'txt1');
INSERT INTO message (email, txt) VALUES ('[email protected]', 'txt2');
INSERT INTO message (email, txt) VALUES ('[email protected]', 'txt3');

2) or use bulk insert instead of it?

INSERT INTO message (email, txt) VALUES ('[email protected]', 'txt1'),('[email protected]', 'txt2'),('[email protected]', 'txt3');

We are talking about tens pieces of data per INSERT. Which one is better from optimization point of view?

Incidentally, txt is a text type column.

Answer 1

for small datasets it wont really matter. However, in general the bulk insert will be faster.

Answer 2

It depends on your transaction behaviour, as well as latency to the mysql server.

But in general, yes, multi-row inserts are good. But be careful you don't run into the maximum packet size (select @@max_allowed_packet ).

A multiple-row insert gets done in a single transaction, even if autocommit is on. This reduces the number of syncs into the transaction log which is likely to increase insert performance. This is assuming innodb_flush_log_at_trx_commit = 1, which it should be if you care about your data!