Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

CREATE TABLE A (
    `pk` INT(11) NOT NULL,
    `fk` INT(11) NOT NULL,
    PRIMARY KEY `pk_A` (`pk`),
    KEY `idx_fk` (`fk`)
) ENGINE=INNODB;

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 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 at 0:08

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.