Mysql – Why is the SQL statement sometimes slow and sometimes fast

MySQLmysql-workbench

I'm using MySQL version 5.5.45 on Windows Server 2016. I'm executing the following statement in MySQL Workbench version 6.3.

delete FROM childattendance WHERE date < DATE_SUB(NOW(),INTERVAL 2 YEAR) limit 100000;

Essentially I'm deleting any records older than 2 years. I'm not worried about which records get deleted as long as they're older than 2 years. I actually want to delete all records older than 2 years but I'm limiting to 100,000 rows at a time so that the statement actually finishes before losing connection.

The weird thing is that sometimes this statement times out, and other times it executes within a few seconds.

See this screenshot for the result, running the same query five times:

enter image description here

The first time it ran for 11 seconds before losing connection (I don't know what's causing that either) and the second time I ran it and it executed successfully in 2 seconds. Ten minutes later I ran it again, failing twice and succeeding once in between.

I've run the same statement limiting to 30000 rows and had the same issue. Sometimes it goes for 11 seconds and times out. Other times it executes in 2 seconds. And I've seen the same thing with other queries.

Coincidentally mysqldump also loses connection to the server when trying to backup this very same table, although it lasts 13 seconds before dying, and it happens 100% of the time. I don't know if that's related.

This table has approximately 6.4 million rows in 471 MB.

What causes this?

Best Answer

This Answer has a mixture of explanation ("why" slow versus fast), speedup (how to be more efficient), and non-blocking (how to avoid the trouble with mysqldump).

  • If few rows are that old, the main effort is scanning the entire table. This can be helped significantly via INDEX(date)
  • If more than 100K rows are that old, then the main effort is saving 100K rows in case of a undo (rollback). This can be quite costly. Don't do more than 1K at a time.
  • If the count is somewhere in between, then there is both the table scan and some of the undo effort. Note that it may find rows to delete early in the scan, or late -- thereby making this take an unpredictable amount of time.

While the lengthy DELETE is running, other things may be adversely affected -- such as timeouts in mysqldump. So, the goal is to keep the DELETE down to only a few seconds, both by speeding it up and by decreasing how much you bite off at a time.

My Big Delete blog explains several ways to significantly improve your task.

Note that one of my techniques focuses on what to do if you are deleting more than half the table. This involves copying over the rows to keep. (See the details on RENAME TABLE, etc)

Related Question