List:General Discussion« Previous MessageNext Message »
From:Keith Ivey Date:January 28 2005 7:45pm
Subject:Re: Why MySQL is very slow in dropping indexes?
View as plain text  
Homam S.A. wrote:

> 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.

See http://dev.mysql.com/doc/mysql/en/alter-table.html , 
especially these bits:

"Note that if you use any other option to ALTER TABLE than 
RENAME, MySQL always creates a temporary table, even if the data 
wouldn't strictly need to be copied (such as when you change the 
name of a column). We plan to fix this in the future, but 
because ALTER TABLE  is not a statement that is normally used 
frequently, this isn't high on our TODO list."

"As of MySQL 4.0, this feature can be activated explicitly. 
ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating 
non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE 
KEYS then should be used to re-create missing indexes. MySQL 
does this with a special algorithm that is much faster than 
inserting keys one by one, so disabling keys before performing 
bulk insert operations should give a considerable speedup."

You want to DISABLE, not DROP, the keys.

-- 
Keith Ivey <keith@stripped>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
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