List:General Discussion« Previous MessageNext Message »
From:mos Date:June 11 2006 1:37am
Subject:Re: dropping primary key - 99%CPU
View as plain text  
At 04:29 PM 6/10/2006, you wrote:
>Hi, Dan,
>
>RE:
> > 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"
>rebuilds everything.
>
>This means if I have a table with multiple indexes, it does not make
>too much sense to create the indexes  separately?
>
>For example:
>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. :(

Mike

Thread
dropping primary key - 99%CPUGaspar Bakos10 Jun
  • Re: dropping primary key - 99%CPUDan Nelson10 Jun
    • Re: dropping primary key - 99%CPUGaspar Bakos10 Jun
      • Re: dropping primary key - 99%CPUmos11 Jun
      • Re: dropping primary key - 99%CPUebergen11 Jun