List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:October 10 2006 1:29pm
Subject:Re: Why does mysql drop index very very slow in a large table?
View as plain text  
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@stripped>
To: bowen <linuzboy@stripped>, mysql@stripped
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=1

Thread
Why does mysql drop index very very slow in a large table?bowen10 Oct
  • RE: Why does mysql drop index very very slow in a large table?Chris \(GE Infra, Non-GE, US\) Wagner10 Oct
    • Re: Why does mysql drop index very very slow in a large table?Rolando Edwards10 Oct
      • Re: Why does mysql drop index very very slow in a large table?bowen11 Oct
        • Re: Why does mysql drop index very very slow in a large table?Rolando Edwards11 Oct