Why does mysql drop index very very slow in a large table?

I have a large table with more than 5M rows, and many indexes. Now I
want to drop some of them. But it seems that mysql can not handle this
very well. It takes a very long time (more than half an hour) to do ,
and make me unbearable.

And I found a mysql bug here. But don't know its current status.

http://bugs.mysql.com/bug.php?id=2364

Anybody know this ?

A workaround is to use mysqlhotcopy to snapshot the table and also only copy the header to the MYI file. Then delete the original and rename the copy back to the original. This will effectively drop all indexes and should take no more time than what the disk takes to copy the .my* files.

Chris, Please read this in its entirety !!!

I learned why 2 years ago while using MySQL 4.1 for Windows
and looking at the folder which contains the .MYDs and .MYIs
while watching 'ALTER TABLE ... DROp INDEX' in action:

If a table T is a MySQL table having four indexes (ndx1,ndx2,ndx3,ndx4)
and you want to 'alter table T drop index ndx3;' here is exactly what
happens under the hood:

1) MySQL copies T.MYD to a temp table, i.e., S.MYD and a zero byte S.MYI.
2) MySQL does 'alter table S add index ndx1 (...);
3) MySQL does 'alter table S add index ndx2 (...);
4) MySQL does 'alter table S add index ndx4 (...);
5) MySQL deletes T.MYD and deletes T.MYI
6) MySQL renames S.MYD to T.MYD, and renames S.MYI to T.MYI

That's it.

Suppose you wanted to drop all four indexes, you would actually be
performing this series of steps 4 times, you would actaully be doing
an 'alter table T add index ...' 6 times.
Pass 1, 3 indexes builds
Pass 2, 2 indexes builds
Pass 3, 1 index build
Pass 4, 0 indexes builds

The reverse is even worse.

Given the same MyISAM table T having four indexes (ndx1,ndx2,ndx3,ndx4)
and you want to 'alter table T add index ndx5 (...);' here is exactly
what happens:

1) MySQL copies T.MYD to a temp table, i.e., S.MYD and a zero byte S.MYI.
2) MySQL does 'alter table S add index ndx1 (...);
3) MySQL does 'alter table S add index ndx2 (...);
4) MySQL does 'alter table S add index ndx3 (...);
5) MySQL does 'alter table S add index ndx4 (...);
6) MySQL does 'alter table S add index ndx5 (...);
7) MySQL deletes T.MYD and deletes T.MYI
8) MySQL renames S.MYD to T.MYD, and renames S.MYI to T.MYI

In fact, let take the worst possible scenario:

Given the same table T with no indexes and you want to add 5 indexes,
here is exactly what happens (brace yourself, seriously):

1 MySQL copies T.MYD to a temp table, i.e., S1.MYD and a zero byte S1.MYI.
2 MySQL does 'alter table S1 add index ndx1 (...);
3 MySQL deletes T.MYD and deletes T.MYI
4 MySQL renames S1.MYD to T.MYD, and renames S1.MYI to T.MYI
5 MySQL copies T.MYD to a temp table, S2.MYD and a zero byte S2.MYI.
6 MySQL does 'alter table S2 add index ndx1 (...);
7 MySQL does 'alter table S2 add index ndx2 (...);
8 MySQL deletes T.MYD and deletes T.MYI
9 MySQL renames S2.MYD to T.MYD, and renames S2.MYI to T.MYI
10 MySQL copies T.MYD to a temp table, S3.MYD and a zero byte S3.MYI.
11 MySQL does 'alter table S3 add index ndx1 (...);
12 MySQL does 'alter table S3 add index ndx2 (...);
13 MySQL does 'alter table S3 add index ndx3 (...);
14 MySQL deletes T.MYD and deletes T.MYI
15 MySQL renames S3.MYD to T.MYD, and renames S3.MYI to T.MYI
16 MySQL copies T.MYD to a temp table, S4.MYD and a zero byte S4.MYI.
17 MySQL does 'alter table S4 add index ndx1 (...);
18 MySQL does 'alter table S4 add index ndx2 (...);
19 MySQL does 'alter table S4 add index ndx3 (...);
20 MySQL does 'alter table S4 add index ndx4 (...);
21 MySQL deletes T.MYD and deletes T.MYI
22 MySQL renames S4.MYD to T.MYD, and renames S4.MYI to T.MYI
23 MySQL copies T.MYD to a temp table, S5.MYD and a zero byte S5.MYI.
24 MySQL does 'alter table S5 add index ndx1 (...);
25 MySQL does 'alter table S5 add index ndx2 (...);
26 MySQL does 'alter table S5 add index ndx3 (...);
27 MySQL does 'alter table S5 add index ndx4 (...);
28 MySQL does 'alter table S5 add index ndx5 (...);
29 MySQL deletes T.MYD and deletes T.MYI
30 MySQL renames S5.MYD to T.MYD, and renames S5.MYI to T.MYI

MySQL Copied T.MYD 5 times
MySQL Copied T.MYI 5 times
MySQL Created an Index 15 times

In fact, for table T with no indexes and you want to add N indexes
MySQL will copy the MYD N times
MySQL will copy the MYI N times
MySQL will run 'alter table add index' N(N+1)/2 times if adding an index
MySQL will run 'alter table drop index' N(N-1)/2 times if dropping an index
Here is a chart on the number of 'alter table drop or add index' commands are executed:

N Number of ALTER TABLE ADD INDEX Number of ALTER TABLE DROP INDEX
----- ------------------------------- --------------------------------
1 1 0
2 3 1
3 6 3
4 10 6
5 15 (Count from past example) 10
6 21 15
7 28 21
8 36 28
9 45 36
10 55 45
11 66 55
12 78 66
13 91 78
14 105 91
15 120 105
16 136 120
17 153 136
18 171 153
19 190 171
20 210 190

This is not speculation.
This is not exaggeration.
As I have said repeatedly, THIS IS EXACTLY WHAT HAPPENS !!!

LOL You propbably wondering at this point how to handle dropping indexes.
Here is an interesting way you may want to try:

Given the same MyISAM table T having four indexes (ndx1,ndx2,ndx3,ndx4)
and you want to 'alter table T drop index ndx3;' try this instead:

1) create table T1 like T;
This creates an empty table T1 with indexes ndx1,ndx2,ndx3 and ndx4.
2) alter table T1 drop index ndx3;
This drops index ndx3 on the empty T1, which should be instantaneous.
3) insert into T1 select * from T;
This will populate table T and load all three(3) indexes for T1 in one pass.
4) drop table table T;
5) alter table T1 rename to T;

Please notice that at no time is a temp table secretly being used.

In fact, the number of steps is exactly the same regardless how many indexes you want.
Just make sure that empty table T1 has all the indexes you want before loading.

Please Try This Method and let me know how it worked for you.

Thank You for reading this whole thing

----- Original Message -----
From: Chris Wagner (GE Infra, Non-GE, US) <chris.wagner@ge.com>
To: bowen <linuzboy@gmail.com>, mysql@lists.mysql.com
Sent: Tuesday, October 10, 2006 2:53:08 AM GMT-0500 US/Eastern
Subject: RE: Why does mysql drop index very very slow in a large table?

A workaround is to use mysqlhotcopy to snapshot the table and also only copy the header to the MYI file. Then delete the original and rename the copy back to the original. This will effectively drop all indexes and should take no more time than what the disk takes to copy the .my* files.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=redwards@swmx.com

>
> 1) create table T1 like T;
> This creates an empty table T1 with indexes ndx1,ndx2,ndx3 and ndx4.
> 2) alter table T1 drop index ndx3;
> This drops index ndx3 on the empty T1, which should be instantaneous.
> 3) insert into T1 select * from T;
> This will populate table T and load all three(3) indexes for T1 in one pass.
>
Insert millions of rows into table should be very slow, and obviously
be not efficient. Can I use `cp -f T.MYD T1.MYD` instead ?

> 4) drop table table T;
> 5) alter table T1 rename to T;
>

bowen wrote:
>>
>> 1) create table T1 like T;
>> This creates an empty table T1 with indexes ndx1,ndx2,ndx3 and ndx4.
>> 2) alter table T1 drop index ndx3;
>> This drops index ndx3 on the empty T1, which should be instantaneous.
>> 3) insert into T1 select * from T;
>> This will populate table T and load all three(3) indexes for T1 in one
>> pass.
>>
> Insert millions of rows into table should be very slow, and obviously
> be not efficient. Can I use `cp -f T.MYD T1.MYD` instead ?
>
>> 4) drop table table T;
>> 5) alter table T1 rename to T;
>>

Not safely.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

You could do that. You must also create a blank T1.MYI.
Even then, MySQL will still go through its death-defying
series of copies and index rebuilds anyway.

You could look into the myisam_repair_threads system variable.
Current MySQL installations have it set to 1. If this is greater than 1,
MySQL will rebuild all indexes in parallel by assigning an index to be
rebuilt on a dedicated thread. I think this feature is currently in beta in
MySQL 5.1.

The suggestion I made is in probability the best way to handle your large table.
It is, therefore, imperative that you tune your server to accommodate this task.
Perhaps you should examine the system variable 'bulk_insert_buffer_size'
(this was called 'myisam_bulk_insert_tree_size' in MySQL 4). This variable
governs the amount of cache per index repair to use for three kinds of bulk
inserts. Here are the three types:

1) LOAD DATA INFILE ...
2) INSERT INTO T1 VALUES (...),(...) ... (...);
3) INSERT INTO T1 SELECT * FROM T;

In the suggestion I made, I recommended bulk insert type #3 which is step 3.
The default value for 'bulk_insert_buffer_size' is 8388608 (8M). Try raising
this value to 32M, 64M, 128M, or even 256M and see what happens. I hope this helps.

----- Original Message -----
From: bowen <linuzboy@gmail.com>
To: mysql@lists.mysql.com
Sent: Tuesday, October 10, 2006 9:33:36 PM GMT-0500 US/Eastern
Subject: Re: Why does mysql drop index very very slow in a large table?

>
> 1) create table T1 like T;
> This creates an empty table T1 with indexes ndx1,ndx2,ndx3 and ndx4.
> 2) alter table T1 drop index ndx3;
> This drops index ndx3 on the empty T1, which should be instantaneous.
> 3) insert into T1 select * from T;
> This will populate table T and load all three(3) indexes for T1 in one pass.
>
Insert millions of rows into table should be very slow, and obviously
be not efficient. Can I use `cp -f T.MYD T1.MYD` instead ?

> 4) drop table table T;
> 5) alter table T1 rename to T;
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=redwards@swmx.com