MySQL: Alter Table Disable Keys

MySQL Disable Indexes Command

When inserting,updating or deleting a high percenblege of the data from a table, it is a best pratce to disable indexes. Especaily, when doing a bulk load into a empty table.  To disable indexes, one can drop all the index keys and recreate them later. Or, we can use Alter Table table name Disable Keys to deactivate  indexes  on a MyISAM table.

To do that, before performing the sql statements that involve with insert, update or delete, issue the following command:

         mysql> alter table tablename disable keys;

Now perform the tasks; for example:

        mysql> insert into tablename(...) values( ...);

After the task has been completed, issue the following command to reactivate the indexes:

         mysql>alter table tablename enable keys;

ALTER TABLE tablename DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE tablename ENABLE KEYS tells MySQL to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. When using ALTER TABLE tablename DISABLE KEYS, please keep in mind the following:

•  ALTER TABLE tablename DISABLE KEYS will keep UNIQUE keys active.
• Using ALTER TABLE tablename DISABLE KEYS requires ALTER, INSERT, CREATE and the INDEX privilege for that table.
• While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.
• ENABLE KEYS and DISABLE KEYS were not supported for partitioned tables prior to MySQL 5.1.11.

 References:

• MySQL Manual: ALTER TABLE Syntax.
• Other References On The Web.