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?