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.

Others also read:

Related:

Posted July 1, 2008 by Casey Bisson
Categories: Technology. Tags: , , , , .

2 Comments

  1. Comment by Tony Palmer on July 24, 2009 4:52 pm

    Er is this not just repeating the mysql documentation?

    Reply
  2. Comment by Casey Bisson on August 7, 2009 8:37 am

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

    Reply

Comments RSS TrackBack Identifier URI

Leave a comment

 

User contributed tags for this post:

mysql bulk update (112) - mysql disable index (99) - mysql DISABLE KEYS (73) - mysql update performance (61) - mysql turn off indexes (30) - mysql update optimization (29) - mysql optimize update (26) - mysql insert index (25) - Mysql bulk update performance (23) - mySQL bulk updates (23) - mysql optimize insert (23) - mysql disable indexes (23) - mysql bulk insert (22) - mysql update speed (22) - mysql enable keys (21) - MySQL disable indexing (21) - bulk update mysql (20) - DISABLE KEYS mysql (20) - disable index mysql (19) - mysql index insert (18) - mysql turn off indexing (17) - mysql bulk insert performance (16) - mysql optimize inserts (16) - mysql turn off index (15) - MySQL speed up inserts (15) - alter table disable keys (14) - mysql update optimize (13) - mysql insert optimization (13) - table (12) - mysql bulk delete (12) - mysql bulk inserts (11) - mysql update large table (11) - mysql disable indices (11) - optimize mysql update (10) - disable index in mysql (10) - mysql temporary disable index (10) - mysql optimize updates (10) - mysql disable index update (10) - mysql temporarily disable indexes (10) - optimize update mysql (9) - optimizing inserts (9) - optimize insert mysql (9) - optimize mysql insert (9) - mysql temporarily disable indexing (9) - mysql turn index off (9) - disable indexes mysql (9) - optimizing updates mysql (8) - php mysql bulk update (8) - mysql optimizing inserts (8) - mysql insert optimize (8) - Mysql bulk insert index (8) - php mysql bulk insert (8) - alter table enable keys (8) - Dastane Bahal (7) - mysql update disable keys (7) - mysql alter table performance (7) - mysql bulk update query (7) - optimizing mysql insert (7) - mysql enable index (7) - mysql index disable (7) - enable keys (6) - optimize inserts mysql (6) - speed up mysql insert (6) - mysql temporary table speed (6) - mysql temporary disable indexes (6) - mysql performance big tables insert (6) - mysql optimizing updates (6) - bulk updates mysql (6) - optimizing mysql inserts (6) - mysql index update (5) - mysql bulk insert disable index (5) - mysql disable index updates (5) - mysql alter table optimization (5) - mysql delete disable keys (5) - MySQL large insert (5) - turn off indexes mysql (5) - mysql optimize update query (5) - mysql optimize delete (5) - Updates for MySQL (5) - mysql alter table disable keys (5) - mysql alter table enable keys (5) - mysql performance update (5) - bulk inserts mysql (4) - mysql update disable indexes (4) - optimize mysql inserts (4) - mysql temporarily disable index (4) - very large tables mysql inserts (4) - mysql optimize for bulk insert (4) - mysql insert query optimization (4) - alter table disable index (4) - speed up mysql inserts (4) - mysql index off (4) - disable index for mysql insert (4) - faster mysql inserts (4) - mysql insert disable index (4) - mysql delete disable index (4) - mysql update disable index (4) - mysql index turn off (4) - mysql optimizing update queries (4) - optimize updates mysql (4) - mysql index table for inserts (4) - insert mysql index (4) - mysql speedup inserts (4) - mysql turning off indexes (4) - bulk updates in mysql (4) - php mysql insert optimization (4) - mysql disable keys enable keys (4) - mysql big table remove index speed (4) - mysql bulk load speed (4) - mysql large table optimization (3) - mysql large tables (3) - mysql optimize large table (3) - mysql spatial optimization (3) - ALTER TABLE `post` ENABLE KEYS (3) - mysql bulk operations (3) - mysql disable index temporarily (3) - how to index mysql tables wordpress (3) - temporary disable indexing mysql (3) - php mysql update performance (3) - optimize update query in mysql (3) - alter table mysql (3) - optimizing mysql updates (3) - mysql table optimization (3) - mysql insert index performance (3) - mysql update bulk (3) - mysql optimize large inserts (3) - speed up inserts mysql (3) - mysql speed up updates (3) - optimizing inserts in mysql (3) - mysql turn off indices (3) - update table mysql (3) - mysql optimize delete large table (3) - disable index temporary mysql (3) - mysql insert and index (3) - mysql optimize insert large tables (3) - mysql turn indexing off (3) - mysql index bulk insert (3) - mysql bulk insert update (3) - mysql insert ENABLE KEYS (3) - how to optimize index2.php (3) - bulk insert mysql php (3) - mysql enable indexing (3) - php mysql insert speed (3) - mysql optimize insert update (3) - turn index off mysql (3) - mysql bulk updates performance (3) - optimizing large mysql tables (3) - mysql indexes off (3) -
Truemors Powered By WordPress WordPress Survey Tools