Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm doing some experimentation in MySQL 5.1.53 and I've run across a case that I just don't understand.

    `pk` INT(11) NOT NULL,
    `fk` INT(11) NOT NULL,
    PRIMARY KEY `pk_A` (`pk`),
    KEY `idx_fk` (`fk`)

SET foreign_key_checks=0;

ALTER TABLE `A` ADD FOREIGN KEY `idx_fk` (`fk`) REFERENCES `parent` (`pk`);

As far as I can tell, this should only add the constraint to the table meta-data for future data enforcement and ought to be an O(1) operation - we've turned off actually checking the constraint with "SET foreign_key_checks=0" and it should be using the idx_fk index (specified in the ADD FOREIGN KEY clause - possibly redundant) so it shouldn't have to create anything or iterate through the data in any way.

And yet that operation gets slower the larger the table when I run the ALTER TABLE.

What is it doing and why is that slow?

share|improve this question
It needs to validate the foreign key, so for every row in A it needs to check if the referenced row in parent does exist. – a_horse_with_no_name Mar 15 '14 at 8:59
@a_horse_with_no_name: I thought I turned that off with the foreign_key_checks=0 statement. Does that flag really mean, "check but ignore"? – James Birchall Mar 27 '14 at 0:08

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.