At 04:29 PM 6/10/2006, you wrote:
> > Currently mysql handles all "ALTER TABLE" commands by rebuilding the
> > entire table and all indexes.
>OK, so an "add index" is mapped to "alter table", and the "alter table"
>This means if I have a table with multiple indexes, it does not make
>too much sense to create the indexes separately?
>alter table add index name1 (name1);
>alter table add index name2 (name2);
>alter table add index name3 (name3);
>Instead, something like:
>alter table add index name1 (name1), add index name2 (name2), \
> add index name3 (name3);
>should work better.
Correct. When using Alter on a table, make sure all table changes are done
through 1 Alter command otherwise the data gets reloaded each time the
Alter is run.
I don't know why adding an index or dropping an index MySQL has to copy the
table and reload all of the data back into the table. It just doesn't make
any sense. It can lock the table sure, but why reload the data? All it has
to do is copy the index that is being modified so it can roll it back, but
don't copy all the data and rebuild all the indexes. It's grossly
inefficient if you ask me. :(