Dropping multiple KEYS of a table

Question

Dropping multiple KEYS in a table is a preferred option or one by one is preferred?

“DROP KEY A,DROP KEY B,DROP KEY C"

For more 900 million records in the table.

How does it work internally?

Answer 1

Dropping multiple indexes all in one SQL command is way better. Why ?

SCENARIO #1

ALTER TABLE mytable DROP INDEX ndx1;
ALTER TABLE mytable DROP INDEX ndx2;
ALTER TABLE mytable DROP INDEX ndx3;

This is what would happen

• Creates an empty temp table
• Removes the index from the empty temp table
• Copies the data into the temp table
• Swaps temp table with original table
• Drops original table

In #SCENARIO #1, this would happen three times.

SCENARIO #2

ALTER TABLE mytable
    DROP INDEX ndx1,
    DROP INDEX ndx2,
    DROP INDEX ndx3
;

Would you believe MySQL used to do this transformation 3 times back in 2006 under MySQL 4.1 ? In other words, SCENARIO #2 would be handled like SCENARIO #1. I actually wrote a post in the MySQL AB General Discussion Forum requesting this be changed (Why does mysql drop index very very slow in a large table?).

Someone asked in the DBA StackExchange if MySQL still did this. I wrote a post here saying this is no longer the case (Does MySQL still handle indexes in this way?) and that question references my original MySQL AB post.

What MySQL does today is this:

• Creates a temp table
• Runs the 3 ALTER TABLE commands against the empty temp table
• Copies the data into the temp table ONCE
• Swaps temp table with original table
• Drops original table

Therefore, you can trust that MySQL will do the right thing today.

Your should go with SCENARIO #2.

GIVE IT A TRY !!!

UPDATE 2015-01-02 13:06 EST

You just asked

What will happen to new operations to the table? Where will new records gets stored at the time of copying? Would it take table lock or metadata lock ?

The table will be locked the entire time. Of course, SCENARIO #1 will be locked three times as long as SCENARIO #2. If you cannot take downtime for this, you only have one option : pt-online-schema-change. All your INSERTs, UPDATEs, and DELETEs will be handled properly during the change.

UPDATE 2015-01-03 08:33 EST

@eroomydna just posted a comment.

@rolando, had you considered the effect of dev.mysql.com/doc/refman/5.5/en/innodb-create-index.html and is default on 5.6. Table should not be rebuilt with the above grouped ddl. No need to use pt-osc for this operation.

@eroomydna is right to point this out. The URL he supplied gives insight into this.

For your particular question, @eroomydna says do not need pt-online-schema-change, and he is right in this instance because you are dropping three indexes. Note the Implementation Details of Fast Index Creation, Paragraph 2:

Dropping a secondary index is simple. Only the internal InnoDB system tables and the MySQL data dictionary tables are updated to reflect the fact that the index no longer exists. InnoDB returns the storage used for the index to the tablespace that contained it, so that new indexes or additional table rows can use the space.

You have a 900 million table and probably a lot of index pages for the three indexes. Dropping the index is being done in a logical manner and must invalidate all those pages to make the space reusable. This will also create lots of shared locks allowing reads and blocking writes. So, this operation should be relatively fast. How long it takes I cannot tell you. Since you said you cannot take the downtime, it could seconds, minutes, maybe even hours for a live table with lots of shared locks and blocked writes.

If you have a copy of the table on a DEV or test server, run the DROP KEY A,DROP KEY B,DROP KEY C just so you can see how fast it runs. Then, you can consider whether downtime would be worth it. Nevertheless, If you want the table live and really cannot do the downtime, you should still go with pt-online-schema-change.

As for my answer, it would apply only if

• you drop an index and create an index with the same name
• drop/add the PRIMARY KEY