> > This extreme slowness in dropping a simple index in
> > MySQL defeats the whole strategy of dropping indexes
> > on some tables before a huge insert operation.
> See http://dev.mysql.com/doc/mysql/en/alter-table.html ,
> especially these bits:
> "Note that if you use any other option to ALTER TABLE than
> RENAME, MySQL always creates a temporary table, even if the data
> wouldn't strictly need to be copied (such as when you change the
> name of a column). We plan to fix this in the future, but
> because ALTER TABLE is not a statement that is normally used
> frequently, this isn't high on our TODO list."
> "As of MySQL 4.0, this feature can be activated explicitly.
> ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating
> non-unique indexes for a MyISAM table. 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."
> You want to DISABLE, not DROP, the keys.
Which, btw, is very misleading -- non-unique indices are NOT
keys. They're indices.
I would only consider unique constraints and primary key constraints
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL