Does MySQL still handle indexes in this way? Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it &amp; found this post from 2006, talking about how MySQL handles ADD and DROP index.


  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?
This is how MySQL 4.x did this and it used to severely aggravate me.
In fact, there was a formula I computed on how many such index maneuvers were needed
Table with 0 indexes and adding 1 index tooks 1 temp table
Table with 1 index   and adding 1 index tooks 3 temp tables
Table with 2 indexes and adding 1 index tooks 6 temp tables
Table with 3 indexes and adding 1 index tooks 10 temp tables (I had eyewitnessed this !!!)
.
.
.
Table with n indexes and adding 1 index took (n + 1) X (n + 2) / 2 temp tables
.
.
.
Table with 16 indexes and adding 1 index took 153 temp tables

Good News, MySQL 5.x does not do that !!!
If MySQL 5.x did this, I would be a PostgreSQL DBA today (No offense to PostgreSQL, it is an excellent RDBMS in its own right).
UPDATE
Oh my goodness, I read the post !!! That post came from me !!!
I never thought someone would dig this post up.
Please leave stuff like this dead and buried. Now I am having flashbacks !!!