slow index DROP and CREATE is a top complaint among MySQL users.
Jan and Jani are now working to fix this. I guess in 2006 this problem has
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
----- Original Message -----
From: ""Martijn Tonies"" <m.tonies@stripped>
Sent: Friday, January 28, 2005 10:05 PM
Subject: Re: Why MySQL is very slow in dropping indexes?
>> > 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
> actual keys.
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
> Upscene Productions
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: