0

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?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.