Mysql – How to speed up foreign key creation in MySQL for large tables

foreign keyMySQLmysql-5.7performance

I am trying to set a foreign key constraint on a 5.7 InnoDB table with 30M+ rows.

It now already runs for over 1h on a quad core 64GB server. The processlist outputs the state copy to tmp table for the issued alter table command.

InnoDB_buffer_pool_size is set to 32G and has room. The system does not swap.

Why does the system create a tmp table and can this somehow be increased in performance?

Best Answer

A FOREIGN KEY:

  • Includes an index, which may help performance during JOINs, sometimes significantly.
  • Includes a constraint that guarantees the existence of a matching row in another table.

Some cases of adding an index require copying the table over. Hence the "copy". Apparently, you have such a situation. For further discussion, please provide SHOW CREATE TABLE before the command, and the ALTER TABLE that was adding the FK.

Related Question