Is batch SQL insert good way for mySQL optimization?

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 ('user1@gmail.com', 'txt1');
INSERT INTO message (email, txt) VALUES ('user2@gmail.com', 'txt2');
INSERT INTO message (email, txt) VALUES ('user3@gmail.com', 'txt3');

2) or use bulk insert instead of it?

开发者_开发知识库INSERT INTO message (email, txt) VALUES ('user1@gmail.com', 'txt1'),('user2@gmail.com', 'txt2'),('user3@gmail.com', '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.

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

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!

I think you should look at SQL Bulk Insert. http://msdn.microsoft.com/en-us/library/ms188365.aspx

With Bulk Insert be careful with your triggers. They have to be written to handle Bulk operations.

The suggested BatchSize is 750,000 rows per batch. So when you use Bulk:

• Lock the table
• Disable temporarily the indexes
• Run the bulk insert in batches
• At the end: Enable indexes and release the table.