MySQL clone a table, prune some data and then hotswap their names

Question

We have a 8 million row MySQL table. We want to prune about 2m rows from it, remove some outdated indexes and add new ones. To achieve almost no downtime, our plan is to create a clone of the table (and lock the original), run a script (Ruby) to prune the data that we don't need anymore and finally add the new indexes. Then we would put up the maintenance page on the web app, swap their names (by renaming the tables) and restart the app.

Is this the best way to achieve our final results? Any other tips/suggestions from people that have some something similar to this before? Whats the best way (super cool secret mysql cmds) to clone the table? Best/quickiest way to mass delete rows?

Answer 1

1. All references use a view, not reading directly from old_table
2. Create new_table
3. Select desired records into new_table, don't bring along records records to be deleted
4. Index new_table
5. Update view, replace old_table references with new_table
6. Rename old_table

Deal with old_table when you want.

The real problem is new data during the transition process, which generally means downtime. Hopefully, you can just disable the ability to add data rather than take the entire site down.