Disable Primary Key on MyISAM Table 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?
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.