With MySQL, how long does an "ALTER TABLE ... DISABLE KEYS;" statement last?

Question

If you disable the keys (suspending indexing) on a mysql INNODB table, how long does that setting last?

For a query like:

ALTER TABLE users DISABLE KEYS;

Do the keys get re-enabled at the end of the script? or do they last until you explicitly turn indexing back on?

Solution 1

The Fine Manual (which is well worth Reading) suggests that the effect of that statement lasts until a corresponding ALTER TABLE ... ENABLE KEYS statement is executed.

Solution 2

ALTER TABLE ... DISABLE KEYS and ALTER TABLE ... ENABLE KEYS do not work for InnoDB.

I addressed this back in February 13, 2011.

I did some additional digging and found out from InnoDB's mother company, InnoBase Oy (before being made 7 of 9 in the Oracle Borg), that this is indeed the case.

This link suggests doing DISABLE KEYS and disabling foreign keys together. Although I think both is unnecessary, this apparently worked for someone.

UPDATE 2011-07-18 12:35 EDT

This is a very good question because it expose an evil that was left over in MySQL. The mysqldump program blindly drops DISABLE KEYS and ENABLE KEYS around the creation and load over every table without regard to storage engine. Since DISABLE KEYS and ENABLE KEYS do not work (in the best case has no effect) on InnoDB tables as it works properly for MyISAM, this unknown fact should be better documented by the MySQL community. Oh yea, MySQL is in the middle of the Galactic Empire known as Oracle. I will not be holding my breath on documentation changes forthcoming.

Comments

[John Gardeniers]

Making a statement is one thing. Yelling it out by using both caps and bold is somewhat excessive and completely unnecessary.

[RolandoMySQLDBA]

@John : I am sorry I seem overly enthusiastic. I am not angry with anyone. ALTER TABLE DISABLE/ENABLE KEYS against InnoDB is not well documented. Oracle is not going to improve things either.

[pahnin]

@RolandoMySQLDBA is there any workaround to avoid mysqldump putting DISABLE KEYS and ENABLE KEYS in the dump file?