alter table query taking too long for adding constraint

Question

I want to alter a table called person and want to add foreign key to it using office table

the query I am using is

ALTER TABLE person
ADD CONSTRAINT person_Office_FK
FOREIGN KEY ( Office_id )
REFERENCES Office ( Office_id ) ;

Table office has around 500,000 rows and table person has around 5 million

This query is taking forever i am not sure what is happening.

Answer 1

If Office_id is the primary key of Office, make sure it has a (primary key) index. This will definitely speed up the adding of the constraint.

Also, according to How to temporarily disable a foreign key constraint in MySQL?, you can use

SET FOREIGN_KEY_CHECKS=0;

To disable ALL foreign key constraint checks, possibly this works too when adding them.