Previously ,whenever we needed to create index or alter a heavy Innodb table’s definition in production , we always ended up with lot of time wastage and down time. This is because MySQL usually acquire exclusive lock on this table till alteration get completed and don’t permit other DML operation to execute on same table.Also while doing this mysql do “copying to tmp table” operation which is really lot of time consuming and night mare for all of us because it is not possible to execute same in production.

However , prior to MySQL 5.6 we have plugins to achieve same but a overhead to activate it.

With current version , we have capability to avoid “copy to tmp table” operation using arguments like ALGORITHM and we can avoid locking using LOCK=NONE.

So now we can use:

CREATE INDEX index_name ON table_name (column_name) ALGORITHM=IN PLACE LOCK=NONE;

Let me explain you. Today I have created a b tree index on innodb table with size on disk 15 GB and have record size 30 millions.

On simple alter or create index/drop index , index creation/drop was taking 18-20 minutes and also blocking other DML operation to execute .

After supplying above mentioned argument create index took 2 min 23 seconds and for drop , it took 0.11 seconds. It was a huge difference.

This is because only meta data of this table has been changed and is accessible by other DML operation too.

Let me know , if it is useful for you with time stats and cpu stats.

Regards

Ankit