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?

  • 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
  • 2
    @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
  • May be the ALTER action rather than the foreign key addition. Consider this previous post and notes on MySQL < 5.6 – Cary Reams Oct 15 '18 at 13:09

Your Answer

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

Browse other questions tagged or ask your own question.