Home / server / Questions / 291100
In Process
Daniel Beardsley
Asked: 2011-07-17 17:56:25 +0800 CST

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

  • 772

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?

mysql database database-administration indexing

2 Answers

  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.

    • 8

  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.

    • 5