2

I wanted to speed up a large insert bulk by disabling keys. So I ran the following command on a MyISAM table:

alter table my_table disable keys;

When I execute show index from answer_2011, I see a comment of disabled in all the keys, except for the primary key.

Also, as I'm inserting new rows, I see that my_table.MYI is being modified.

Is there a way to disable or drop the primary key that does not involves having MySQL create temporary tables?

3

Unfortunately, no there isn't because ALTER TABLE ... DISABLE KEYS disables nonunique indexes

For MyISAM tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing indexes. MyISAM 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.

If you have to do a bulk insert, you need to use LOAD DATA INFILE along with a larger bulk_insert_buffer_size. Here is why:

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

| improve this answer | |
2

If you sort the records according to the PRIMARY KEY before loading them, it will run faster.

If you are "replacing" an existing table, do this to avoid essentially all downtime:

  1. CREATE TABLE new LIKE real; -- with all PRIMARY & UNIQUE keys
  2. Load new with data
  3. ALTER TABLE new ADD INDEX ...; -- add all secondary indexes
  4. Optionally sanity check the data in new.
  5. RENAME TABLE real TO old, new TO real;
  6. DROP TABLE old;

Both comments apply to both MyISAM and InnoDB.

You would be better off migrating to InnoDB; this would best be done by changing the Engine of new to InnoDB just after Step 1.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.