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.

Related:

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

No Comments

No comments yet.

Comments RSS TrackBack Identifier URI

Leave a comment

 

User contributed tags for this post:

mysql bulk update (31) - mysql disable index (27) - mysql update optimization (8) - table (7) - mysql enable keys (7) - Dastane Bahal (6) - mysql performance big tables insert (6) - mysql bulk insert (6) - mysql turn off indexes (6) - mysql update large table (5) - optimizing inserts (5) - mySQL bulk updates (5) - mysql disable index update (5) - optimizing updates mysql (5) - mysql update performance (5) - mysql optimize for bulk insert (4) - disable index mysql (4) - very large tables mysql inserts (4) - disable index for mysql insert (4) - mysql update disable keys (4) - mysql temporary disable index (4) - mysql optimize update (4) - mysql alter table performance (4) - mysql optimize updates (4) - MySQL speed up inserts (4) - alter table disable keys (3) - mysql DISABLE KEYS (3) - temporary disable indexing mysql (3) - speed up mysql insert (3) - mysql insert optimize (3) - mysql optimize delete large table (3) - mysql insert index performance (3) - mysql table optimization (3) - mysql bulk update query (3) - Mysql bulk update performance (3) - MySQL disable indexing (3) - mysql turn off index (3) - bulk update mysql (3) - php mysql bulk update (3) - mysql update speed (3) - mysql update optimize (3) - Updates for MySQL (3) - mysql updates (2) - mysql update speed insert (2) - mysql turn off index temporarily (2) - mysql delete disable keys (2) - mysql temporarily disable index (2) - gooleporno (2) - mysql spatial optimization (2) - disable index temporary mysql (2) - mysql turn off index updates (2) - disabling index temporary mysql (2) - bulk insert mysql indices (2) - off.index in mysql (2) - mysql optimizing large updates (2) - MySQL disabling indexes update during (2) - mysql temporary table speed (2) - does indexing help in optimizing insert (2) - optimize update query in mysql (2) - mysql optimize insert (2) - php mysql disabling indexes (2) - mysql optimizing update (2) - mysql update performance index (2) - mysql optimize update query (2) - optimize update mysql (2) - how to optimize mysql INSERT and UPDATE queries (2) - temporarily disable index updates (2) - temporarily disable index updates mysql optimize (2) - mysql indices temporary table (2) - bulk inserts mysql queries (2) - bulk insert query in mysql (2) - bulk insert mysql optimize (2) - mysql alter table optimization (2) - mysql update table indexes (2) - removing indexes from a large mysql table (2) - temporarily disabling indexes mySQL (2) - mysql optimize alter table (2) - optimize update with where mysql (2) - php mysql bulk insert (2) - mysql index comment disabled (2) - mysql bulk insert disable index (2) - MySQL large insert (2) - mysql indexes disable before insert (2) - mysql temporary disable indexes (2) - mysql turn off indexes on table (2) - mysql optimization for insert and update (2) - optimize insert mysql (2) - Mysql bulk insert index (2) - mysql optimize inserts (2) - optimize inserts mysql (2) - mysql bulk inserts (2) - bulk inserts mysql (2) - mysql optimize no update table (2) - KEYS FOR WWW SEX.COM (2) - mysql optimize bulk insert (2) - mysql large table update (2) - mysql insert and index (2) - mysql delete from large table (1) - mysql optimize delete query (1) - mysql table with many indexes and inserts (1) - bulk deletion mysql (1) - optimized update mysql (1) - mysql speedup deletes updates (1) - how to speed up the insertion in mysql (1) - wordpress database tags modify bulk (1) - alter a large mysql table (1) - bulk mysql (1) - big inserts into table MySQL (1) - how to optimize mysql inserts (1) - mysql speeding up select from large tables (1) - insert index performance mysql (1) - lego tables inserts (1) - optimize mysql insert (1) - mysql temporary tables optimization (1) - lots of inserts mysql (1) - mysql temporarly table (1) - speed up update queries (1) - Ubuntu mysql optimize tables (1) - mysql bulk insert stop index (1) - php mysql table comment (1) - mysql stop indexing insert (1) - mysql disable index and keys (1) - optimizing mysql updates delete (1) - mysql insert ... select disable keys (1) - temporary disable index mysql (1) - speeding up update query mysql (1) - alter table en ubuntu (1) - mysql optimize delete/insert vs update (1) - mysql keys (1) - mysql update versus insert speed (1) - mysql insert optimize speed (1) - mysql turn off unique (1) - optimizing insert in mysql (1) - optimizing inserts in mysql (1) - MYSQL optimize for updates (1) - how to optimize mysql update query for large table (1) - mysql insert optimization (1) - mysql char index on huge table (1) - mysql speed up updates (1) - php mysql bulk query (1) - speed up mysql insterts php (1) - optimize mysql insert queries (1) - mysql altering big table (1) - alter table index on temporary mysql (1) - mysql disable table index (1) - mysql temp table disable (1) - MySQL insert optimized performance (1) - mysql speed up insert queries on website (1) - alter table enable keys mysql (1) - insert update two tables mysql (1) -
Truemors Powered By WordPress WordPress Survey Tools