MySQL Bugs: #61515: slow drop foreign key

Bug #61515	slow drop foreign key
Submitted:	14 Jun 2011 16:32	Modified:	11 Jul 2013 5:33
Reporter:	Cyril SCETBON	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S4 (Feature request)
Version:	5.5.9	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	alter, Drop, fk, slow

Description:
Hi,

when I drop foreign keys and associated indexes (secondary indexes) MySQL copy the table to make a new one. I don't why it does it as I drop only secondary indexes and constraints

How to repeat:
n/a

It's not correlated to the secondary index as if I only suppress a fk the whole table is rebuilt

Suggested fix:
none

here is a way to reproduce it :

CREATE TABLE `t7` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` char(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

CREATE TABLE `t8` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` char(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`),
  CONSTRAINT `fk1` FOREIGN KEY (`c`) REFERENCES `t7` (`c`) ON UPDATE CASCADE
) ENGINE=InnoDB;

insert into t7(c) values('c');
insert into t8(c) values('c');
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
insert into t8(c) select c from t8;
alter table t8 drop foreign key fk1;

when doing the alter table, "show processlist" shows :

+----+------+-----------+------+---------+------+-------------------+-------------------------------------+
| Id | User | Host      | db   | Command | Time | State             | Info                                |
+----+------+-----------+------+---------+------+-------------------+-------------------------------------+
| 20 | root | localhost | test | Query   |    1 | copy to tmp table | alter table t8 drop foreign key fk1 |

I think this is a clearly documented limitation. Check http://dev.mysql.com/doc/refman/5.5/en/innodb-create-index-limitations.html, please:

"MySQL 5.5 does not support efficient creation or dropping of FOREIGN KEY constraints. Therefore, if you use ALTER TABLE to add or remove a REFERENCES constraint, the child table is copied, rather than using Fast Index Creation."

You're right Valeriy, I missed it in the documentation :(
It's really disappointing that removing a constraint needs to recreate my 10GB table ....

Yes, that's unfortunate, but prevents some bugs...

Adding and dropping FK constraints is way too slow.

6 hours for a 16G table when no data is modified / changed in the base table.
InnoDB should be smarter than that.

set to FR, verified

Any fix?

The problem is being addressed. Although I can not communicate on future release calendar, it should be solved in 5.6 or later.

great news !

This issue should be addressed in latest 5.6 release with new online alter interface. The drop FK no longer require table rebuild

I like it :)