From: Martijn Tonies
Date: January 28 2005 8:04pm
Subject: Re: Why MySQL is very slow in dropping indexes?
List-Archive: http://lists.mysql.com/mysql/179118
Message-Id: <09bd01c50574$9e4d79f0$3802a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit


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