8

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?

14

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).

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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