"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.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
