Optimizing Inserts/Updates On MySQL Tables

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.

5 thoughts on “Optimizing Inserts/Updates On MySQL Tables

  1. Yes, i know. This article is from 2009. But it was the first result of google and if someone else get this as the first result, i think it´s important to correct this a little bit.

    It is correct, that disabling the keys will speed up inserting data into a mysql table. But don´t disable the keys, if you want to update your tables! An update without any indexes will slow down your updates significantly because every update will need a table scan. Please don´t do that :)

    • Not true – as far as updates are done properly – using primary keys – no table scan is required:
      only “the non-unique indexes are disabled”

Comments are closed.