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.

Answer 2

You can use:

SET FOREIGN_KEY_CHECKS=0;

I typically do my own foreign key checking before I do this, though:

SELECT
  Person.*
FROM person
LEFT JOIN Office ON (person.Office_id = Office.Office_id)
WHERE Office.Office_id IS NULL
;

That query should return nothing if you are safe to turn off the FOREIGN_KEY_CHECKS.

Answer 3

The question is still not obsolete. I just got the same issue here and found a workaround in 5 steps:

1. Duplicate the table: CREATE TABLE table2 LIKE table1;

2. Apply the constraint foreign key: ALTER TABLE..FOREIGN.... bla bla bla

3. Dump data from table1 to table2: INSERT INTO table2 SELECT * FROM table1;

4. Delete table1: DROP TABLE table1;

5. Rename table2: RENAME TABLE table2 TO table1;

All in one:

CREATE TABLE table2 LIKE table1;
ALTER TABLE..FOREIGN.... bla bla bla;
INSERT INTO table2 SELECT * FROM table1;
DROP TABLE table1;
RENAME TABLE table2 TO table1;

Answer 4

Before adding your constraint, make sure that there's a clustered index on office_id on the office table and a non-clustered index on office_id on the person table.

Remember that every occurrence of office_id on the person table needs to check against every office_id record. This will also speed things up if you ever have to delete an office record.

You don't want to disable the checks, since your constraint will be untrusted and you won't get the performance benefit a foreign key gives you in the query optimizer.