List:General Discussion« Previous MessageNext Message »
From:Peter Brodersen Date:September 23 2004 3:45am
Subject:Reason for a slow "remove index"-operation?
View as plain text  
Hi,

I have a MyISAM-table with a couple of fields, and about half a million
rows (datafile is 15MB, indexfile is 10MB) . The server is running under
MySQL 4.0.21.

Creating an index on a field takes about two minutes. That's fully
expected, as the server has to perform an initial sorting for all
500000 rows.

When I try to remove the index, it takes about one minute. I'm just not
sure why this operation is taking so long. I would have thought that the
index nearly could be cut right out of the index file, since no meddling
with the datafile or the individual rows in the index, or any
recalculation of trees would have to take place.

When dropping the index there is almost full utilization of the cpu, and
SHOW PROCESSLIST for most of the time shows the state as "copy to tmp
table" (and then a few seconds of "Repair by sorting", when dropping the
index). I wouldn't think there is an i/o-issue, since the file is pretty
small and the cpu is utilized (instead of a high iowait-state).

What is the reason for an index drop to take that amount of time?


(I'm not dropping indexes on a daily basis - my question is mainly out
of curiousity :) )

-- 
- Peter Brodersen

Thread
Reason for a slow "remove index"-operation?Peter Brodersen23 Sep
  • Re: Reason for a slow "remove index"-operation?Dan Nelson23 Sep