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