Homam S.A. wrote:
> 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.
Keith Ivey <keith@stripped>