MySQL Bugs: #2364: Dropping all indexes from a table is slow

Bug #2364	Dropping all indexes from a table is slow
Submitted:	12 Jan 2004 15:15	Modified:	4 Dec 2013 10:48
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S4 (Feature request)
Version:	4.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When dropping all indexes from a table (ALTER TABLE tblname DROP INDEX `idx1`, DROP INDEX `idx2`, DROP INDEX `idx3`; etc.), the process should return nearly instantly (as with TRUNCATE), but does not.  This is most visible on very large tables.

How to repeat:
Create a large table with several indexes.  Issue a single ALTER TABLE statement to drop all of the indexes.

Suggested fix:
Have mysqld recognize that there are no indexes on the table.

It is not a bug, but a design deficiency. ALTER TABLE now often does a lot of unnecessary work.
Making ALTER TABLE smarter is in the todo.

Have a table with 3 million rows.
Drop index takes 40 minutes and is still not finished.

This should take only seconds?!

Seriously, what's the status of this?
I wish it could be as simple as ignoring a MYI file which the server will later delete asynchronously. Maybe it would be better to have a single MYI per index.

This is still an issue on MySQL 5.0.  Any hope of getting this fixed?

We support several databases with our product, with MySQL our preferred one.  But...in our upgrades we occasionally drop indexes, create new indexes, etc.  It takes 10 seconds on MS SQL Server, and about 5 minutes on MySQL (doing the same logical thing!).

Here is some more inside information on just how retarded the current way
mysql drops indexes actually is. Wow!

http://lists.mysql.com/mysql/202489

AFAIK...this is still the method used in MySQL 5.0.32

The five step way to drop indexes reasonably quickly on large tables
is very effective.

It isn't just dropping an index that could easily be improved, although that is a complaint I have as well. What is taking so long?

Another thing that could be solved with ALTER TABLE is when you have varchar columns that are set to NOT NULL with no default, and you realize you want their default to be ''.

You would think that since the previous value of the column was NOT NULL DEFAULT NULL that it would take just a couple of seconds adjusting things in memory and the .frm file for the ALTER TABLE to finish. Instead, I had a table with 2GB of data, and I ended up waiting for 18 minutes. Since I have 20 columns to update, it becomes a day-long project, on something that could have taken 2 minutes, and only that long because you have to type out all the commands.

This is ridiculous design, and I'm guessing it should be easy to patch, unless the original ALTER TABLE code is so bad or non-modularized that an entire re-write of ALTER TABLE is necessary.

Are we waiting on someone to design the 'ultimate perfect way to accomplish everything' related to improving ALTER TABLE? If so, give it up and just throw in some quick fixes (probably just some if statements that bypass some checks) that will drastically improve a few things, like the drop index statement. The 'ultimate fix for everything' can come later, we've been waiting for this thing for many years.

This is the kind of feature or lack of a feature that drives SysAdmins crazy and makes people think twice about recommending MySQL when they are starting a new project or pushing for something at a company that hasn't previously used MySQL.

A number of ALTER TABLE operations which only modify the .frm file and do not require regeneration of data or indexes are able to be performed "instantly" from MySQL 5.1.  We're adding more such operations over time.

Daniel, in 1 alter table statement you given many modify commands, see example below.

create table test (col1 varchar(10) NOT null, col2 varchar(10) NOT null);

alter table test
  modify col1 varchar(10) NOT null default ''
, modify col2 varchar(10) NOT null default '';

show create table test;
+-------+---------------------------------------------
| Table | Create Table                                                  
+-------+---------------------------------------------
| test  | CREATE TABLE `test` (
  `col1` varchar(10) NOT NULL default '',
  `col2` varchar(10) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------

Fast alter table is part of 5.1, and fast add/drop index is implemented.

Sorry, we only added infrastructure for fast add/drop index.
The implementation itself wasn't done.

5.1.51 with innodb plugin.  dropping any index is fast due to fast alter table.

mysql> show table status like 't1';
+------+--------+---------+------------+---------+----
| Name | Engine | Version | Row_format | Rows    | Avg
+------+--------+---------+------------+---------+----
| t1   | InnoDB |      10 | Compact    | 4194351 |
+------+--------+---------+------------+---------+----
1 row in set (0.20 sec)

mysql> alter table t1 drop index a;
Query OK, 0 rows affected (0.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

Added as part of WL#5534 to MySQL 5.6