List:General Discussion« Previous MessageNext Message »
From:ebergen Date:June 11 2006 1:43am
Subject:Re: dropping primary key - 99%CPU
View as plain text  
Gaspar Bakos 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?
>   
Because alter table rebuilds the entire table you should try to cram as 
much into one alter table as possible. If you are building multiple 
indexes or dropping adding columns do them all in the same alter table. 
Like this:
alter table t add index (foo), drop column bar, add column baz;
> 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.
>
> I am dealing with a table of ~100Gb size that will be purely readonly,
> and i am trying to optimize the reads by creating indexes on the
> columns that are most popular in the "where" statements".
>
> G
>
>   

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