Large MySQL insert

Anon

SETUP: A file of 30,000+ lines, tab-seperated, is sittingon the server. By clicking a button, the file should be parsed line by line and inserted into 2 MySQL tables: one a contact table that gathers the info; the other a joiner table between the contact and another table which will require the id of each record inserted into the the contact table.

PROBLEM: I wrote a script that works for 40-50 records (maybe more) but when you get up in the thousands, it chokes horribly. Any suggestions on what I should be reading to spead this up and reduce overhead (memory management, buffering, whatever)?

mithril

Ryan,

Have you tried using the set_time_limit() function? The default time limit on script execution is 30 seconds, but you can up this if need be. For example, the script in the phpMyAdmin program from phpwizard.net set's the time out to 10000 seconds:

@set_time_limit(10000);

Don't know if this is what you're looking for, but it might help.

Cheers,

Geoff A. Virgo

Anon

Geoff,

Well, that's actually helpful to know for another script, but it's not my problem here. The problem here isn't the timeout (which I set higher in the php.ini file), but it's something to do with system resources, I think. I figure if I could find a way to run the script in the background or something, it might be better. As it is, it starts out fast, but after about 1000 of the 30,000 records, it starts to die. It's craps out by about 4000.

Thanks, anyway.

Anon

Ryan,

Show us the code you are using. We can't tell you what to optimize without a starting point.

Also, what are the contents of the file? By that I mean what are the sizes of the tab seperated fields? There's a big difference in how to process this if the fields are relatively small or if they are huge.

Anon

The fields are small: name, city, state, zip, email.

The way I tried it, I open the file and read it line by line using fgets:

$f = fopen($file, "r");
while (!feof($f)) {

 $buffer = fgets($f, 1092); // I've tried this with various buffer sizes

Then I split the line into its parts as follows:

 list($name,$city,...) = split($buffer);

Once I have the variables created, I have to insert them into one database table, then get the ID assigned to that record by MySQL and insert that into another table.

So with each iteration, the script has to:
1- split the line into its parts
2- insert into table 1
3- pull out id (using LAST_INSERT_ID) from table 1
4- insert into table 2.

Any ideas?

Oh, the database connection and db selection are done OUTSIDE the loop (thankfully, I'm not that dumb, although I'm not sure how much that would add to overhead with PHP's use of persistent connections).

Anon

Oops...

I am using the split function properly in the code: split("\t", $buffer).

I just forgot to put the delimiter into my example.

Duh.