Can I create index on a table in a MySQL database while in use

Question

And if I can, any reason (performance/stability) why I shouldn't?

score 18 · Accepted Answer · answered Jan 13 '11 at 20:39

18

Note that if your table is using the InnoDB Plugin storage engine (which I highly recommend), if it's a secondary index (almost surely the case), you can still read from the table (i.e. it's non-blocking to SELECT statements).

answered Jan 13 '11 at 20:39

Riedsio

1,377
1
14
18

2

MySQL 5.6 update (feb 2013): you can now read and write to the table with online DDL - http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html – Morgan Tocker Dec 18 '13 at 04:55
@MorganTocker several years later, but your link is now broken... – Shaul Behr Jun 28 '21 at 15:35

score 16 · Answer 2 · answered Jan 11 '11 at 19:45

16

Yes you can. It will lock the table you're adding an index to while it's being created. If the table is large, it may take awhile as it has to read each row while building the index.

answered Jan 11 '11 at 19:45

Zach Wily

161
1
2

1

Facebook has a solution for doing this online. Haven't tried it out myself: https://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932 – esilver Oct 13 '11 at 22:34

Can I create index on a table in a MySQL database while in use

2 Answers2