create an index without locking the DB

Question

I have a table with 10+ million rows. I need to create an index on a single column, however, the index takes so long to create that I get locks against the table.

It may be important to note that the index is being created as part of a 'rake db:migrate' step... I'm not adverse to creating the index manually if that will work.

UPDATE: I suppose I should have mentioned that this a write often table.

Answer 1

MySQL NDBCLUSTER engine can create index online without locking the writes to the table. However, the most widely used InnoDB engine does not support this feature. Another free and open source DB Postgres supports 'create index concurrently'.

Answer 2

you can prevent the blockage with something like this (pseudo-code):

create table temp like my_table;
update logger to log in temp;
alter table my_table add index new_index;
insert into my_table select * from temp;
update logger to log in my_table;
drop table temp

Where logger would be whatever adds rows/updates to your table in regular use(ex.: php script). This will set up a temporary table to use while the other one updates.