List:General Discussion« Previous MessageNext Message »
From:SGreen Date:January 28 2005 7:28pm
Subject:Re: Why MySQL is very slow in dropping indexes?
View as plain text  
"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
Thread
Why MySQL is very slow in dropping indexes?Homam S.A.28 Jan
  • Re: Why MySQL is very slow in dropping indexes?SGreen28 Jan
  • Re: Why MySQL is very slow in dropping indexes?Keith Ivey28 Jan
  • Re: Why MySQL is very slow in dropping indexes?Sasha Pachev28 Jan
  • Re: Why MySQL is very slow in dropping indexes?Martijn Tonies28 Jan
Re: Why MySQL is very slow in dropping indexes?Heikki Tuuri30 Jan