Hello,
I have problem with inserting large amount of records with one click, I have table where I should insert 100000+ records per one click on web form. Script manage to insert 27000(approx) records but then it just hangs and to nothing. I could try with increasing time of script execution but that is not real solution because there could be more than 100000 records.
Is there some way to import so many records?
Is the data that gets inserted generated all at the same time? If not try generating and inserting the data in batches, say 5,000 records at a time. The error indicates that the memory needed by php for the operation exceeded that which it had available. What is the memory_limit set for in php.ini
If it is in a SQL file or CSV file you can tell mysql to import directly from the file rather than trying to load it into memory and send one row at a time.
With the query (presumably a join query) that your using to get the data, are you using the “evil” SELECT * or are you just specifying the fields that you need?
Do you need to process the results of the select query using php, before inserting into the other table? If not, you can skip reading the data into php and just let mysql handle it http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
If you really do need to process with php, you don’t need to buffer the entire result set into php’s memory at the same time. See mysql_unbuffered_query(). You can do similar with mysqli and pdo.
Try to do the inserts in the actual database, instead of passing the data via php, like crmalibu suggested.
There should be absolutely no application that will have to do so many inserts user action… especially a web form (where people double click).
If you do, once you get 2-3 people to click that in the same time, your server will slow down.
If the server is a bit slow, and someone gets bored and stops the process half way, you could end up with inconsistent database entries (usually there’s no commit in mysql).
What you should do if you REALLY need those inserts, is validate the user’s click, and add the the “action” in a queue for background processing later. (maybe a cron every minute to make a few inserts)