Recently I decided to do some work on a sqlsnapshot utility and tried to figure out the best way to do a bulk update on the database.
Here are the test scripts I used to generate the SQL:
mysqld Ver 5.5.31-0ubuntu0.12.10.1 for debian-linux-gnu on i686 ((Ubuntu))
Running on a VM in VMWare Player
First off: single SQL statements are bad.
This SQL should look like this:
On average the mass UPDATE took anywhere from 20-30 seconds to execute! The replace only took 0.30-0.12 seconds!
Now of course, different schemas may yield different results - but even the delete/insert was 0.20-0.12 seconds (ie DELETE FROM test, INSERT INTO...). From what I see it appears that MySQL doesn't wait for the user to stop entering SQL queries. There really wouldn't be a good way to detect this though, besides assuming that if a user hasn't entered a SQL statement for the past second to run the transaction.
Wait transactions....let's take a look at those. In transactions you can run a bunch of queries, and a SQL will run them in a shadow table, and when you tell the SQL engine "COMMIT" it will commit all of the queries you just made.
So I modified the update SQL script.
Running it now takes about 0.70 seconds instead of 20 seconds! What a difference. Adding transaction to the replace script turned it into a consistent 0.12 seconds. However, it didn't seem to do anything for deleting/inserting and inserting data.
Transactions are good for:
Transactions do no improve:
And when you can use REPLACE over UPDATE.