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?