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:
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.
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?