On Thu, Feb 19, 2009 at 2:14 PM, Claudio Nanni <claudio.nanni@stripped> wrote:
> I need to add an index on a table on a production server.
> It is one 7Gb InnoDB table with single .ibd file (one_file_per_table),
> the index creation on preprod server took 40 minutes but table was smaller.
> I tried to add the index but was locking all applications on production and
> had to kill it.
> I have requested a maintenance window but it will take long time.
> Since this application is scanning like crazy I'd like to do it a.s.a.p.
> Do you have any hint for a non locking solution?
Try something like this:
create table new_table like old_table;
alter table new_table add key(.....);
insert into new_table select * from old_table where <primary_key>
between ? and ?;
Repeat the last statement until nearly finished, then:
lock tables new_table write, old_table write;
insert into new_table select * from old_table where <primary_key> > ?;
rename table old_table backup, new_table old_table;