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

Question

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)?

Answer 1

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

Answer 2

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.