mb1987 mb1987 - 13 days ago 5
MySQL Question

alter table query taking too long for adding constraint

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

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.

Comments