suggestions on stopping the madness

[john mulligan]

We are running mysql 5.0.45 on Linux and have a table of gargantuan proportion. There is a primary index which is an auto-generated column. The problem (at least one of them) is that there is also an index made up of 7 different columns. I'd like to drop this index. However, in this table there are 163555583 rows; the data length is 24779948032; the index_length is 22771924992; and we add about 671,000 rows a day to this table (and right now there is no delete routine). Dropping the index will take forever. Oh, and ibdata1 is currently a whopping 49377443840;

So here is what I'd like to do tackle the problem. Recreate the table structure without data and without the 7 column index. Then repopulate the last few months of data (via insert into... select from) which would be roughly 20800000 rows. Then rename the gargantuan table to something else and rename the new table to the original. Then I'll drop the gargantuan table. And finally, institute a daily delete routine for deleting old rows out of the new table.

Does this sound like the best method (or at least a good one)? And will the drop table allow us to reuse that space (not on disk but within the innodb file)?

Thanks in advance for any suggestions/comments...

John