List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:January 28 2005 7:56pm
Subject:Re: Why MySQL is very slow in dropping indexes?
View as plain text  
Homam S.A. wrote:
> 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 MySQL, most ALTER TABLE operations involve a full reconstruction of the 
table, so dropping a key often takes almost as long as adding one. For a long 
time, optimizing them has not been a priority.

Also note that in MyISAM all keys are stored in one MYI file. So dropping a key 
is not as easy as just deleting a file.

-- 
Sasha Pachev
AskSasha Linux Consulting
http://www.asksasha.com

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