MySQL thousands of updates, slowing down.

Question

I need to run a PHP loop for a total of 100, 000 times (about 10, 000 each script-run), and each loop has about 5 MySQL UPDATES to it. When I run the loop 50 times, it takes 3 sec. When I run the loop 1000 times, it takes about 1300 sec. As you can see, MySQL is slowing down ALOT with more UPDATEs.

This is an example update:

mysql_query("UPDATE table SET `row1`=`row1` +1 WHERE `UniqueValue`='5'");

This is generated randomly from PHP, but I can store it in a variable and run it every n loops.

Is there any way to either make MySQL and PHP run at a consistent speed (is PHP storing hidden variables?), or split up the script so they do?

Note: I am running this for a development purposes, not for production, so there will only be 1 computer accessing the data.

Answer 1

Turn the auto_commit off and commit the transaction explicitly after N times (if you are working with InnoDB tables).

Answer 2

Add index on UniqueValue and delete index from row1 and other not used indexses.

Answer 3

Use prepared statements. One of their two main benefits is that repeated queries are more efficient.

Answer 4

Apart from indexing the row1 and UniqueValue fields, also ensure that they are using an numeric datatype like INTEGER and remove those singlequotes from the UniqueValue in the WHERE clause.

mysql_query("UPDATE table SET `row1`=`row1` +1 WHERE `UniqueValue`=5");

Else MySQL would need to massage it forth and back to/from String which is a performance cost.

Answer 5

Using something like this should be loads faster:

mysql_query("UPDATE table SET `row1`=`row1` + 1 WHERE `UniqueValue` IN (5, ... 10)");

Assuming you've got an index on uniqueValue ofcourse. You can create queries up to 8MB with the default settings but 1MB should be more than enough for now. It will be a huge query but much faster.

You could also temporarily disable the index updates entirely, that should make things a bit faster too:

ALTER TABLE table DISABLE KEYS;
ALTER TABLE table ENABLE KEYS;