MySQL MyISAM – How to Disable Primary Key on MyISAM Table

myisamMySQL

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?

Best Answer

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.

Related Solutions

Mysql – Select “,* from table

You can do the following:

CREATE TABLE tablename_temp (id int AUTO_INCREMENT PRIMARY KEY)
ENGINE=InnoDB 
SELECT * FROM tablename;

ALTER TABLE b
ADD UNIQUE INDEX some_index_name (your_original_key);

You can find the documentation here: http://dev.mysql.com/doc/refman/5.1/en/create-table-select.html And a small example on SQLFiddle.

MySQL – Auto Increment Primary Key Differences in MyISAM and InnoDB

All you can guarantee with an auto-increment is that (unless you jiggle about with it to reset the value) the next value that is generated will be higher than the last one. It is usually just one higher, but you should never rely on this: beyond simply always increasing further detail is undefined behaviour.

As to why it might be different with different table types: different tables types vary in their locking/transaction/concurrency behaviours (myISAM being pretty simple/naive and InnoDB far less so in order to offer the referential integrity and performance benefits). It may be that InnoDB was being cautious and allowing for concurrent transactions, and when you inserted rows from details in animals (step 5) "reserved" the IDs it thought it might need but overestimated so didn't use one of them leaving the gap. myISAM probably just applied a table lock for the duration of that update so no concurrency could happen, so it has no need to reserve IDs in case of concurrent insert operations (so consequently won't leave gaps if it overestimates the number of rows output).

Best Answer

Related Solutions

Mysql – Select “,* from table

MySQL – Auto Increment Primary Key Differences in MyISAM and InnoDB

Related Question