«   |   »

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.

Posted on Tuesday, July 1st, 2008 at 1:32 pm in Technology   |  RSS feed

Tags: , , , ,

5 Comments to “Optimizing Inserts/Updates On MySQL Tables”

    
  1. Tony Palmer
    July 24, 2009 at 4:52 pm

    Er is this not just repeating the mysql documentation?

  2. Casey Bisson
    August 7, 2009 at 8:37 am

    @Tony Palmer: it’s an obscure piece of the documentation that I didn’t want to lose or forget.

  3. Michael
    June 19, 2011 at 2:01 pm

    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 :)

    • damund
      September 23, 2011 at 7:39 am

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

    

User contributed tags for this post: