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"" <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
