CREATE INDEX MySQL 5.6.13 On Production Database

Question

I am running MySQL 5.6.13 and I would like to run a CREATE INDEX ... BTREE statement on my production database.

The table is InnoDB and has ~ 4 million rows, and I would like very much not to lock it.

According to the docs, it appears as if this statement will not completely lock my table and return quickly. But, I wanted a second opinion before I made this change.

Would it be safe to create this index?

Answer 1

By default, InnoDB in MySQL 5.6 will perform a read lock while creating the index, so you can still have other concurrent clients SELECT from the table, but not do insert/update/delete on that table while the index is being created.

You can optionally allow the index creation to be completely online and not even do the read lock:

ALTER TABLE my_table ADD INDEX a (a), LOCK=NONE;

See http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html for more details about online DDL statements in MySQL.

Also see this blog posted today from a MySQL Community Manager: Top 10 advances to availability since MySQL 5.5

PS: It's not necessary to specify BTREE for the index type. InnoDB supports only BTREE indexes, so it ignores that option.