CODEWITHSUNDEEP
mysqlperformanceindexingsql-drop
Garrett
Garrett

Reputation: 89

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?

Upvotes: 5

Views: 11736

Answers (1)

Rick James
Rick James

Reputation: 142208

SHOW CREATE TABLE -- If it says ENGINE=MyISAM, the DROP is performed this way:

  1. Copy the entire table data over into a temp (slow due to lots of I/O)
  2. Rebuild all the remaining indexes (very slow, in some cases)
  3. 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.)

Upvotes: 7

Related Questions