Many inserts in a row - why is the occasional one really slow?
Posted on 2006-07-04
I am inserting many records into a mysql table
PLease note I am using
INSERT INTO table SET colname='data', colname2='data2'....
and inserting one at a time
as opposed to
INSERT INTO table (colname1,colname2,colname3) Values (data1,data2,data3)
and inserting several records at a time
This is because I need a confirmation of successful entry for each record.
My script inserts anywhere up to 250 records at a time, and is uploading records from my WINXP/apache/php/mysql setup to a linux/apache/php/mysql webserver, using my 1500K ADSL connection.
Of the 250 records, 98% of them insert in under 0.2 seconds. But a few records take anywhere up to 2 mins to insert... all records are the same length, so I do not know why this is happening.
My PHP upload script takes up to 6 mins to execute instead of about 20 seconds... and this happens roughly 1 record in 50. This was not happening until today - no dramas for over 12 months.
I tried a check table - it's OK.
There are a few indexes on the table but since most records go fast I doubt the indexes are the cause.