Timeline for Deleting millions of rows in MySQL
Current License: CC BY-SA 3.0
5 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 8, 2018 at 4:24 | comment | added | Buttle Butkus |
I was able to delete 100,000 records at a time (DELETE FROM table WHERE id <= 100000, then 200000, etc). Each batch took between 30 seconds and 1 minute. But when I previously tried to delete 1,300,000 at once, the query ran for at least 30 minutes before failing with ERROR 2013 (HY000): Lost connection to MySQL server during query. I ran these queries in the MySQL client on the same virtual machine as the server, but maybe the connection timed out.
|
|
| Jan 27, 2018 at 11:36 | comment | added | simplifiedDB | deleting 1 M record in one go will kill your server | |
| Feb 17, 2017 at 19:15 | comment | added | user2693017 | This is the only solution that worked for me with a 100GB table. Select with limit 1000 was just a few milliseconds but the delete with the same query took an hour for just 1000 records, although a SSD is in place. Deleting this way is still slow but at least a thousand rows per second and not hour. | |
| Apr 13, 2016 at 19:16 | review | Late answers | |||
| Apr 13, 2016 at 19:19 | |||||
| Apr 13, 2016 at 18:57 | history | answered | rich | CC BY-SA 3.0 |