Hi! On Thu, Feb 19, 2009 at 2:14 PM, Claudio Nanni <claudio.na...@gmail.com> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
