4

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?

2 Answers 2

6

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.

4
  • Nice find. Can I ask, where did you find this?
    – genesis
    Oct 10, 2011 at 5:17
  • I updated my answer how I learned this. Oct 10, 2011 at 18:08
  • I didn'T even know you're the owner of this report.
    – genesis
    Oct 10, 2011 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 !!! Oct 10, 2011 at 20:18
2

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.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .