"Homam S.A." <homam_sa@stripped> wrote on 01/28/2005 02:27:51 PM:
> Dropping an index on a MyISAM table should be
> instantaneous. It should only take as long as deleting
> the idx file from the file system. But it's taking
> almost as long as creating the index itself!
> Here's my queries and time they took:
> /*[10:58:17 AM][367172 ms]*/ alter table MyTable add
> index (MyIndex)
> /*[11:20:21 AM][183891 ms]*/ alter table MyTable drop
> index MyIndex
> In MS SQL server, if the index isn't clustered and
> there are no other indexes in the table, dropping the
> index is instantaneous.
> 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.
> Why doesn't it just delete the index file and clear
> whatever cached buffers it has of it?
You neglected to mention which version of MySQL you are running. This
behavior may have been fixed in newer versions as yours is not the first
post I can remember seeing on this general topic.
Unimin Corporation - Spruce Pine