Is there an optimal number of records OR data size I can insert in one request?

Using the Mysql multi insert statement (Inserting several records in one INSERT statement). Is there an optimal number for the number of records I can insert 开发者_如何学运维in one go, Or to the data size (bytes)?

the limit is to the size of the row , one row query is limited ,

The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns).

u can defined it in the mysql.cnf file

the parameter is

[mysqld]
max_allowed_packet=16M

or set like

shell> mysql --max_allowed_packet=32M

link :

http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

The more the merrier. It's much faster to do one massive insert, rather than various simple ones. You eliminate a lot of overhead doing massive inserts, rather than many small ones.

Yes, there is. It is not the maximum allowed by your system and settings.

With careful benchmarking it can be shown that if you keep increasing the number of rows, after a point the multi insert stops yielding any benefits, and then slowly starts to perform worse.

The optimum depends on your system, your settings and the dataset. Running benchmarks will help you locate your optimal size.