List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:January 28 2005 8:04pm
Subject:Re: Why MySQL is very slow in dropping indexes?
View as plain text  
> > 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.

Which, btw, is very misleading -- non-unique indices are NOT
keys. They're indices.

I would only consider unique constraints and primary key constraints
actual keys.

:-)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.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