This is a short post about DELETE data from huge table in MySQL. Most of us experienced, deleting huge record from MySQL tables taking long time, sometimes hours to complete deleting millions of records. Also, on production servers it locks the other table operations as well. Recently, I deleted around 70 million record from a production database in less than an hour. There are multiple workarounds to do this, however I am writing about the two methods which are frequently used by me for this operation.
- Using intermediate table.
- Delete data in small chunks.
Before we proceed with using any of these methods, make sure the
table has required indexes on where clause and you have a copy of
the table as backup.
Using intermediate table: In this method, create a new table with similar data structure and copy only required data. Rename the original table as archive or backup table and Rename the …