List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:January 30 2005 4:59pm
Subject:Re: Why MySQL is very slow in dropping indexes?
View as plain text  
All,

slow index DROP and CREATE is a top complaint among MySQL users.

Jan and Jani are now working to fix this. I guess in 2006 this problem has 
been removed.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

----- Original Message ----- 
From: ""Martijn Tonies"" <m.tonies@stripped>
Newsgroups: mailing.database.myodbc
Sent: Friday, January 28, 2005 10:05 PM
Subject: Re: Why MySQL is very slow in dropping indexes?


>
>> > 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
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/mysql?unsub=1
> 

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