Menu

Optimizing Inserts/Updates On MySQL Tables

#ALTER TABLE #databases #DISABLE KEYS #ENABLE KEYS #mysql #optimization

When doing a bulk insert/update/change to a MySQL table you can temporarily disable index updates like this:

``` ALTER TABLE $tbl_name DISABLE KEYS ```

…do stuff…

``` ALTER TABLE $tbl_name ENABLE KEYS ```

From the docs:

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.