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 (33) - mysql disable index (31) - table (9) - mysql update optimization (9) - mysql enable keys (7) - mysql turn off indexes (7) - mySQL bulk updates (6) - Dastane Bahal (6) - mysql update performance (6) - mysql performance big tables insert (6) - mysql bulk insert (6) - MySQL speed up inserts (5) - mysql disable index update (5) - optimizing updates mysql (5) - optimizing inserts (5) - disable index mysql (5) - mysql update large table (5) - mysql alter table performance (4) - mysql update disable keys (4) - mysql temporary disable index (4) - Mysql bulk update performance (4) - bulk update mysql (4) - mysql optimize for bulk insert (4) - MySQL disable indexing (4) - mysql optimize insert (4) - mysql optimize updates (4) - disable index for mysql insert (4) - very large tables mysql inserts (4) - mysql optimize update (4) - mysql update optimize (4) - mysql alter table optimization (3) - temporary disable indexing mysql (3) - mysql insert optimize (3) - mysql DISABLE KEYS (3) - mysql insert index performance (3) - mysql bulk update query (3) - mysql update speed (3) - mysql table optimization (3) - speed up mysql insert (3) - mysql turn off index (3) - php mysql bulk update (3) - alter table disable keys (3) - mysql temporary table speed (3) - mysql optimize delete large table (3) - mysql bulk inserts (3) - Updates for MySQL (3) - disable index temporary mysql (2) - mysql indexes disable before insert (2) - mysql alter table disable keys (2) - MySQL large insert (2) - bulk insert query in mysql (2) - mysql temporarily disable index (2) - mysql optimizing large updates (2) - mysql turn off index temporarily (2) - bulk inserts mysql queries (2) - optimize insert mysql (2) - mysql enable index (2) - how to optimize update mysql (2) - mysql temporarily disable indexes (2) - disabling index temporary mysql (2) - mysql optimize inserts (2) - mysql update speed insert (2) - mysql bulk delete (2) - how to optimize mysql INSERT and UPDATE queries (2) - mysql indices temporary table (2) - bulk insert mysql optimize (2) - mysql optimize update query (2) - mysql insert and index (2) - mysql turn off index updates (2) - optimize update query in mysql (2) - mysql update performance index (2) - removing indexes from a large mysql table (2) - bulk insert mysql indices (2) - php mysql disabling indexes (2) - MySQL disabling indexes update during (2) - temporarily disabling indexes mySQL (2) - mysql updates (2) - mysql temporary disable indexes (2) - off.index in mysql (2) - mysql turn indexing off (2) - mysql optimize no update table (2) - mysql delete disable keys (2) - optimize update with where mysql (2) - temporarily disable index updates mysql optimize (2) - bulk inserts mysql (2) - optimizing inserts in mysql (2) - optimize inserts mysql (2) - mysql index comment disabled (2) - mysql bulk insert disable index (2) - mysql optimize alter table (2) - Mysql bulk insert index (2) - mysql turn off indexes on table (2) - mysql performance large tables delete (2) - mysql disable index temporarily (2) - mysql optimizing update (2) - php mysql bulk insert (2) - alter table disable index (2) - mysql large table update (2) - temporarily disable index updates (2) - mysql optimization for insert and update (2) - mysql optimize table updates (2) - mysql update table indexes (2) - KEYS FOR WWW SEX.COM (2) - gooleporno (2) - mysql spatial optimization (2) - does indexing help in optimizing insert (2) - optimize update mysql (2) - mysql optimize bulk insert (2) - mysql update post (1) - MYSQL insert on huge table (1) - mysql 5 index comment disabled (1) - mysql disable table index (1) - mysql query disable keys (1) - mysql insert faster than update (1) - mysql disabled keys update (1) - mysql delete alter table disable keys (1) - disable index temporarily mySQL (1) - mysql temp table disable (1) - mysql disable keys all tables (1) - mysql turn off privileges (1) - mysql large table indexes delete (1) - update table mysql (1) - large table insert mysql (1) - mysql insert vs update performance (1) - performing mysql update with php (1) - mysql no-update table (1) - bulk update in mysql (1) - mysql insert disabling index (1) - optimize mysql insert queries (1) - speed up mysql insterts php (1) - speed up mysql inserts (1) - mysql enable index update (1) - php mysql bulk query (1) - php mysql insert performance (1) - mysql optimizing delete insert large tables (1) - cross table inserts mysql (1) - mysql disable keys for insert (1) - enable mysql index enable (1) - mysql optimize insert large tables (1) - mysql delete vs update speed (1) - how to optimize inserting in large tables (1) - MySQL 5.0 indexes to speed up (1) - mysql update versus insert speed (1) - mysql tables optimize (1) - mysql insert, update, delete PHP (1) - alter table index on temporary mysql (1) - mysql altering big table (1) - performance big tables mysql php (1) - turn off indexing during bulk insert (1) - mysql speed up update (1) -
Truemors Powered By WordPress WordPress Survey Tools