Re: deleting data from large fragmented InnoDB table

[Rick James]

I need more info to give you a complete answer; here are some questions and some discussion:

But how big will the new table be?

True, DELETE will not release any space in the case of innodb_file_per_table=1. If you had =0 when creating the fragmented table, and have =0 for the new table, space will be released inside ibdata1, and reused. So, this is a rare case where =0 is beneficial.

Take a serious look at pt-online-schema-change. It will do the 'chunking' for you, plus lots of other details.

Let's see
SHOW CREATE TABLE
SHOW TABLE STATUS
Samples of the SELECTs that need each of the indexes
and how much disk space you have to spare.

Also, what version are you running? Some newer versions can add an index without copying the table over.