6 messages in com.mysql.lists.mysqlRe: Why MySQL is very slow in droppin...
FromSent OnAttachments
Homam S.A.28 Jan 2005 11:27 
SGr...@unimin.com28 Jan 2005 11:28 
Keith Ivey28 Jan 2005 11:45 
Sasha Pachev28 Jan 2005 11:56 
Martijn Tonies28 Jan 2005 12:04 
Heikki Tuuri30 Jan 2005 08:59 
Subject:Re: Why MySQL is very slow in dropping indexes?
From:Heikki Tuuri (Heik@innodb.com)
Date:01/30/2005 08:59:19 AM
List:com.mysql.lists.mysql

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.to@upscene.com> 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=myo@freebsd.csie.nctu.edu.tw