>> I have a mysql database with a table which has grown huge. The data
>> itself take up around 6GB and the indexes around 14GB. So I decided to
>> remove some of the indexes because I'm running low on space. But the
>> "ALTER TABLE xxx DROP INDEX zzz" result in a "copy data to tmp table"
>> which takes up a lot of space too - which is pretty uncool when space is
>> what you miss!
>>
>> Does anyone know a way to solve the problem - remove indexes without
>> getting this "copy to tmp table" before the removal of the index?
>
>I have read that any type of ALTER TABLE statement causes a full rebuild
>of the table. During this rebuild, MySQL typically requires additional
>disk space, as it is essentially storing the table twice for a brief period.

I don't think this is true of *ALL* ALTER TABLE statements, in
particular ALTER TABLE foo DISABLE KEYS and ALTER TABLE foo ENABLE
KEYS, which would be rather pointless if they required re-building
the table. Probably stuff like changing the default character set
for a table or setting the auto increment value don't rebuild the
table either. However, pretty much anything that requires conversion
of the data or adding, deleting, or re-ordering columns or indexes
will.

>I don't know how to get around this problem. Perhaps dump the data for
>the whole table to a SQL file (using mysqldump), then drop the table,
>then restore the table without the indexes you want to eliminate. It
>might take some creative use of grep to filter out the CREATE INDEX
>statements in the dump file as you input the SQL script to `mysql`.

I think you can dump the data, DELETE the data (not drop the table),
alter the table as you wish (ALTER TABLE takes very little temporary
disk space on an empty table) then reload the data. mysqldump can
dump just the data without the table structure. For MyISAM tables,
DELETE without a WHERE clause will shrink the files. For InnoDB tables,
it should free up the space to be re-used when you reload the data.
Of course, you do need space somewhere for the output of mysqldump.

>If you are using MyISAM tables, it might be temping to simply remove the
>.MYI files, which store index data. I can't say if this is safe to do,
>or if it will cause more problems.

I recall a lot of problems trying to recover from this (fsck deleted
a .MYI file during a messy crash caused by a brownout during an
ALTER TABLE), even with REPAIR TABLE. I think I eventually created
a table with identical structure, then copied the .MYI file over
to replace the missing one, then did REPAIR TABLE. Don't just
delete the .MYI file. It's not worth it.

Gordon L. Burditt