I’ve created a script that:
– creates a new table MY_TABLE_NEW
– creates a merge-table MY_TABLE_MERGE combining MY_TABLE_NEW and MY_TABLE whereby inserts are done in MY_TABLE_NEW
– renames MY_TABLE into MY_TABLE_OLD and at the same time MY_TABLE_MERGE into MY_TABLE

This effectively creates a merge table with all the data, still accessible through the name MY_TABLE where all new records are moved into MY_TABLE_NEW – which is optimized of course as it is empty.

Then the script slowly starts moving records from MY_TABLE to MY_TABLE. It takes a record (or fifty), deletes them and inserts them. That effectively moves them from MY_TABLE_OLD to MY_TABLE_NEW. That slowly builds a MY_TABLE_NEW which is optimized, while the system continues to work.

The chances of the system needing a specific record while it’s being deleted and inserted are very, very small with the tables I’ve used. Of course that depends highly on the way your system processes data. But for me, it worked flawlessly.

I’ve used this script dozens of times on production systems. It’s coping with interruptions, can be run in parallel on several tables at the same time and has virtually no downsides.

Of course you cannot merge merge-tables, so when you already have merge-tables, this solution may not be for you.

If you’d like to take a look at the script, let me know in a comment below.

Cheers,
Eelko de Vos

http://mysql.rjweb.org/doc.php/deletebig

For time-based purging, PARTITION by “range” is an excellent solution to large DELETEs.

Thanks for responding to my post. I agree with all the points that you have made above. I didn’t intend to imply that the INSERT technique was universally better, only that it was faster in some cases, which was an important factor to me. It certainly has a number of limitations, particularly if you have foreign key relationships or cannot stop writes to the table. My specific use was to get rid of about 50MM rows in over a dozen tables with only soft foreign key relationships (Rails application) and also have an easy rollback in the event that something broke. By doing this, you end up with a complete copy of your table which you can drop at your leisure or move back into place if something goes wrong.

@Art – I brought up the stall issue in my post along with problems caused by ext filesystems. I can’t see this technique being very effective on a live server unless you can guarantee no writes to the table.

At the same I do want to add that Aaron’s suggestion also has a problem on high concurrent MySQL systems where the create/drop will certainly cause stalls in the LRU.