How to Delete Millions of MySQL Rows Without Downtime?

How to Delete Millions of MySQL Rows without Downtime

In the face of a bug that resulted in millions of duplicate rows, finding a delete strategy that minimizes downtime is crucial.

One option involves executing numerous smaller delete queries in a loop. However, this approach can strain the database and lengthen the deletion process.

Another option is to rename the existing table, create a new empty one, perform cleanup on the renamed table, and then merge the data back into the original table. While this method offers minimal interruption, it comes with logistical challenges, including potential data loss and the need for meticulous data merging.

A more efficient solution is to execute the following query in a loop, with a short sleep period between iterations:

DELETE FROM `table`
WHERE (whatever criteria)
ORDER BY `id`
LIMIT 1000

By limiting the deletion to 1000 rows at a time and adding a short sleep interval, this technique allows other queries to interleave and prevents table locking. As the iterations continue, the number of rows affected will gradually decrease until zero remains.

This approach combines efficiency with minimal downtime, making it a suitable choice for deleting large volumes of rows without disrupting site functionality or user experience.

The above is the detailed content of How to Delete Millions of MySQL Rows Without Downtime?. For more information, please follow other related articles on the PHP Chinese website!