Inserting large amount of records per click

[zlaajaa]

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?

Best Regards
Zlatko

[SpacePhoenix]

Any errors returned by either PHP or the database server?

[zlaajaa]

I get
Fatal error: Allowed memory size of…

Regards

[SpacePhoenix]

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

[Dan_Grossman]

Where is the data the script inserts coming from?

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.

[zlaajaa]

Is the data that gets inserted generated all at the same time?

Yes,I first need to match rows from 4different tables and then to insert results in that one table,

ini_set(“memory_limit”,“128M”);

[zlaajaa]

@Dan_Grossman

Where is the data the script inserts coming from?

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.

Everything is in DB, getting results from 4 different tables, generate and insert into one final table.

Regards

[SpacePhoenix]

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?

[zlaajaa]

@SpacePhoenix

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?

Field names are specified in SELECT queries, it would be good if I could just free memory during execution

[eanimator]

use BigDump. it worked for me

[crmalibu]

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.

[zlaajaa]

@crmalibu

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.

mysql_unbuffered_query() was just what I needed… Thanks to all of you

[Vali]

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)