How to speed up foreign key creation in MySQL for large tables? 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?
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 &quot;copy&quot;.  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.