Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles
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
Is this still true? Is his advice still valid?
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;
How do you all handle adding and removing indexes from large tables?