CODEWITHSUNDEEP
mysqlindexinginnodb
Bruno Manzo
Bruno Manzo

Reputation: 374

Creating a index before a FK in MySQL

I have a not so big table, around 2M~ rows. Because some business rule I had to add a new reference on this table. Right now the application is writing values but not using the column. Now I need to update all null rows to the correct values, create a FK, and start using the column.

But this table has a lot of reads, and when I try to alter table to add the FK the table is locked and the read queries get blocked.

There is any way to speed this? Leaving all fields in NULL values helps to speed up (since I think there will be no need to check if the values is valid)? Creating a index before helps to speed up?

In postgres I could create a not valid FK and then validate it(which caused only row lock, not table lock), there is anything similar in MySQL?

Upvotes: 1

Views: 71

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562240

What's taking time is building the index. A foreign key requires an index. If there is already an index on the appropriate column(s), the FK will use it. If there is no index, then adding the FK constraint implicitly builds a new index. This takes a while, and the table is locked in the meantime.

Starting in MySQL 5.6, building an index should allow concurrent read and write queries. You can try to make this explicit:

ALTER TABLE mytable ADD INDEX (col1, col2) LOCK=NONE;

If this doesn't work (like if it gives an error because it doesn't recognize the LOCK=NONE syntax), then you aren't using a version of MySQL that supports online DDL. See https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

If you can't build an index or define a foreign key without locking the table, then I suggest trying the free tool pt-online-schema-change. We use this at my job, and we make many schema changes per day in production, without blocking any queries.

Upvotes: 1

Related Questions