Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 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?

share|improve this question
up vote 13 down vote accepted

This is how MySQL 4.x did this and it used to severely aggrevate 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).


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 !!!

share|improve this answer
I was wondering if it was the same Rolando. – Derek Downey May 12 '11 at 17:56
Well, so it is. Not a lot of digging, as it was the 3rd result on google. If I could filter my search by time I would, believe me. – JIStone May 12 '11 at 17:58
Now I hear Rod Serling echoing in the room !!! – RolandoMySQLDBA May 12 '11 at 17:58
+1 all round - this is hilarious – Jack Douglas May 12 '11 at 18:08
LOL, Rolando, take care of what you write, you might read it some time 5 years from now? :-) – Marian May 12 '11 at 20:01

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.