MySQL Statement Optimization

ringostizar

Hi all,

I'm reading and loading large data amounts from a text file into my MySQL database. It works fine but it takes way too long for such a simple process, so I was wondering if I am not optimizing my MySQL statements correctly. Here's what I'm dealing with:

I'm using PHP to read a data file (csv) containing around 23,000 lines. Each line has about 10 fields (commas). The first field of every line is the id number. For each id number, I am using a SELECT to see if that id already exists in my database. If so, I simply use an UPDATE to update that record, else I use an INSERT to add the record. So, I am essentially hitting my database 2 times for each of my 23,000 lines of data. At a rate of 100 lines per minute, this process takes hours to complete. Is there any way I can combine my statements in one swoop or something to hit my database less? Thank you very much.

inerte

Ok, first of all, before looping the file, put in an array() every ID. Then you can just use in_array() instead of a SELECT per line. You can do it pretty fast with an "binary search", look at the comments on the php manual for in_array()

Then, if you don't find the ID, don't INSERT right away. Use the database multiple insert feature: INSERT INTO table VALUES (1, 'a'), (2, 'b'), (3, 'c'). Just separate the rows with parenthesis and the commas. Concat the records and insert at the end of the loop.

Well, depend on your server. Inserting 23K rows at once might be too slow. You can consider the multiple sql insert statement as "buffered", just do the query every 1K or 2K values.

The UPDATE is the hard part... Go with one per loop iteration where it is needed. If it's still slow, hold the values that should be updated, and after the file loop, use a DELETE and then an INSERT.

If your ID database is a PK or Unique key you can try to use multiple REPLACE INTO.

I have optimized a script from a co-worker using these techniques and was able to go from inserting 5K lines to over 200K... but YMMV.

laserlight

If you can use transactions, use them.

ringostizar

Thanks, inerte. I'll look into that solution.

laserlight wrote:
If you can use transactions, use them.

laserlight, if I am going to use transactions ... since I am running sets of scripts on 23,000 records individually, would I be able to use a simple BEGIN/COMMIT once or would I have to use that 23,000 times as well, once for each sql query?