Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Why is dropping an index on MyISAM table so slow? I can't imagine I'd have social network with 700m records & dropping index.

There are only one-column indexes, all of them are fulltext (I need to switch to normal index as InnoDB don't like fulltext)

DROP INDEX

mysql> DROP INDEX `post_text` ON phpbb_posts;
Query OK, 251171 rows affected (7 min 35.04 sec)
Records: 251171  Duplicates: 0  Warnings: 0

however, ALTER TABLE is faster than that DROP?

mysql> ALTER TABLE phpbb_posts DROP INDEX `post_content`;
Query OK, 251182 rows affected (1 min 45.06 sec)
Records: 251182  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE phpbb_posts DROP INDEX `post_subject`;
Query OK, 251188 rows affected (1 min 32.55 sec)
Records: 251188  Duplicates: 0  Warnings: 0

There is even a bug report for it but no valid solution.

Questions:

  • Why is DROP INDEX slower than ALTER TABLE?
  • Why is dropping the index so slow on such a small table?
share|improve this question

2 Answers 2

up vote 6 down vote accepted

If you want to micromanage the dropping of the index, why not automate yourself ?

CREATE TABLE phpbb_posts_new LIKE phpbb_posts;
ALTER TABLE phpbb_posts_new DROP INDEX post_text;
ALTER TABLE phpbb_posts_new DISABLE KEYS;
INSERT INTO phpbb_posts_new SELECT * FROM phpbb_posts;
ALTER TABLE phpbb_posts_new ENABLE KEYS;
ALTER TABLE phpbb_posts RENAME phpbb_posts_old;
ALTER TABLE phpbb_posts_new RENAME phpbb_posts;
DROP TABLE phpbb_posts_old;

This is essentially what doing the ALTER TABLE would do 'under the hood'

Doing it this way will let you see the timing of each of these internal steps.

UPDATE 2011-10-10 14:08 EDT

I actually researched this personally. I remember complaining to MySQL about how slow adding indexes were when adding or dropping and suggested how they can fix it. That was back in October 2006.

Then, suddenly, out of nowhere, someone asked about this same issue on May 12, 2011. The person posting the question (JIStone) even referenced my original post to MySQL. I did not realize it, until about 7 minutes later, that I was actually addressing my own 5-year-old post.

share|improve this answer
    
Nice find. Can I ask, where did you find this? –  genesis Oct 10 '11 at 5:17
    
I updated my answer how I learned this. –  RolandoMySQLDBA Oct 10 '11 at 18:08
    
I didn'T even know you're the owner of this report. –  genesis Oct 10 '11 at 19:19
    
Wouldn't you know it? Today is Oct 10, 2011 and I posted my suggestion to MySQL Oct 10, 2006, Exactly five years ago today !!! –  RolandoMySQLDBA Oct 10 '11 at 20:18

Why? Because the developers made mistakes?! Why should there be a bug report when it isn't a bug- So wait until it got fixed or fix it yourself- As you even have a workaround you can live with it.

But the proper answer would be that DROP INDEX doesn't lock the table while executing. And therefore it takes more time to allow reading and writing while dropping- And ALTER TABLE locks the table and so can concentrate on the dropping process.

share|improve this answer
    
Huh, the bug exists from 2006. Do you think somebody will fix it? –  genesis Oct 9 '11 at 16:07
    
@genesis-φ Do you think they won't? –  mailq Oct 9 '11 at 16:09
    
I'm not sure. Thanks for edit though –  genesis Oct 9 '11 at 16:17

Your Answer

 
discard

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.