List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:February 20 2009 10:08pm
Subject:Re: InnoDB - CREATE INDEX - Locks table for too long
View as plain text  
Hi!

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;
unlock tables;

Baron
Thread
Left join does not work with Count() as expectedArtem Kuchin19 Feb
  • Re: Left join does not work with Count() as expectedOlaf Stein19 Feb
    • InnoDB - CREATE INDEX - Locks table for too longClaudio Nanni19 Feb
      • Re: InnoDB - CREATE INDEX - Locks table for too longBaron Schwartz20 Feb
        • Re: InnoDB - CREATE INDEX - Locks table for too longClaudio Nanni21 Feb
        • Re: InnoDB - CREATE INDEX - Locks table for too longClaudio Nanni16 Mar
  • Re: Left join does not work with Count() as expectedPeter Brawley19 Feb
  • RE: Left join does not work with Count() as expectedMartin Gainty19 Feb