I need to do multiple inserts in a mySQL table using a PHP script, say Table1 but the number may exceed 1000 so I want to know if there is an insert limit as either memory or number of rows in mySQL? Also if this limit varies from server to server?
-
execution time and memory depend on server to server which effect the bulk entry but you can set from your code and .htaccess fileabhayendra– abhayendra2016-04-28 17:59:06 +00:00Commented Apr 28, 2016 at 17:59
-
execution time is not a concern, I want to know if there is limitation in insertion so I can write my script keeping that in mind so it does not become useless in future.Zohaib Aslam– Zohaib Aslam2016-04-28 18:02:16 +00:00Commented Apr 28, 2016 at 18:02
Add a comment
|
2 Answers
The primary limiting factor here is how long a statement MySQL will accept. This is defined with the max_allowed_packet system variable.
Setting this in your my.cnf file or equivalent to 1GB is usually fine. Anything beyond that gets to be absurd but you're free to increase it if you have the memory to accommodate it. Remember that both client and server will often need this in memory, so if this is on the same machine 2GB is theoretically required. Don't get too crazy here.
An INSERT statement with multiple VALUES entries can otherwise load in as much data as you can fit in that packet size.
If you're doing really big imports, LOAD DATA INFILE is often a lot faster but you will have to prepare your data in a format it can accommodate. CSV files are usually the easiest to work with here.
Comments
google max_allowed_packet. set it as high as your memory allows. if strlen($YourQueryString) <= this limit, you are OK to send it
for example, once I wanted to maximize throughput of php script, so I collected rows and counted they strlen until I hit max_allowed_packet, then send it. In cycle. It was much faster than limiting on number of rows.