Gaspar Bakos wrote:
> Hi, Dan,
>> 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.
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".