drop index gives me a "copy to tmp table"

[Ask Josephsen]

Hi

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?


Thanks

Ask

[onedbguru@firstdbasource.com]

is the index that you are trying to drop a Primary Key index for the
table? can you drop the tmp table when it is done or do you exhaust
your disk space trying to drop the index?

[Ask Josephsen]

<onedbguru@firstdbasource.com> wrote in message
news:1144416220.340765.303270@e56g2000cwe.googlegr oups.com...

> is the index that you are trying to drop a Primary Key index for the
> table? can you drop the tmp table when it is done or do you exhaust
> your disk space trying to drop the index?

I run out of diskspace before it is done. As far as I can read from the
mysql documentation an "ALTER" statement causes the table to copied to the
altered or something like that... The type of the indexes I want to delete
are INDEX - it is not the primary key.

[Bill Karwin]

Ask Josephsen wrote:

> Hi
>
> 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 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`.

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. You might want to create a small
table in your "test" database and see what happens when you remove the
..MYI files of existing indexes, and then try to drop an index.

Things get even more troublesome if you are using InnoDB. The default
InnoDB file, "ibdata1" stores all tables and indexes in the one file.
The file does not shrink as you drop tables and indexes. You have to
dump _all_ your InnoDB tables, manually delete the whole "ibdata1" file,
and then restore the tables.

Regards,
Bill K.

[Gordon Burditt]

>> 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

[Ask Josephsen]

Gordon Burditt skrev:

> 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.

Sounds like the solution I'll go for - hope I cam make mysqldump work
being a windows guy :) And that the mysqldump can handle 6 GB of data...

Thanks
Ask