Drop index query is slow
This is the query I'm trying to execute:
DROP INDEX id_index on table;
I've been able to drop indexes quickly in the past, but this query ran for almost an hour before I gave up on it. What could be causing this slow pace?
Answer
SHOW CREATE TABLE -- If it says ENGINE=MyISAM, the DROP is performed this way:
- Copy the entire table data over into a temp (slow due to lots of I/O)
- Rebuild all the remaining indexes (very slow, in some cases)
- Rename to replace the existing table (always fast)
This can be very slow, depending on the size of the table. This is because of all the disk I/O.
If it says ENGINE=InnoDB, things could be better. But it still matters whether you are DROPping the PRIMARY KEY or not. And possibly whether the KEY is involved in a FOREIGN KEY constraint. I assume old_alter_table is set to OFF.
http://dev.mysql.com/doc/refman/5.6/en/alter-table.html has a lot of details. What you needed to say was ALGORITHM=INPLACE. You probably got ALGORITHM=DEFAULT, and I don't see in the doc what the default is.
ALGORITHM=COPY acts like I mentioned above for MyISAM.
ALGORITHM=INPLACE should take very little time, regardless of the table/index size.
(Be sure to check the details of ALTER for whichever version you are running. There have been several significant changes in recent major versions.)