MYSQL Indexing and Data Updation | Updating Indexed Fields in MSQL Database

When you update MYSQL database, if some fields in the update query are under an index, the index table also is updates... on large tables and many queries this may cause extreme over load... to avoid this, use the below method to disable the index update before your update and then enable it after it.

This sure saves you from a private server hosting force.

ALTER TABLE table_name DISABLE KEYS
-----DO STUFF HERE------
ALTER TABLE table_name ENABLE KEYS

ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes.

ALTER TABLE ... ENABLE KEYS then should be used 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.

Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

While the non-unique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.