DROP TABLE on a huge InnoDB table

Question

I have an innodb table with over 140 million rows taking 26GB. I would like to drop this table however this is taking way too long, probably days. How can I speed up this query?

processor: Intel® Xeon® E3-1220 4 Cores x 3.1 GHz (3.4 Turbo Boost) ram: 12 GB DDR3 ECC

Answer 1

Perhaps you can disable foreign key checks and unique checks:

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;
DROP TABLE ...;

This has to work since a standard mysqldump embeds commands like these and a DROP TABLE before creating and reloading it. I have written about before

• Jun 11, 2013 : Speeding up mysqldump / reload
• May 26, 2013 : Is it possible to force drop of mysql tables with FK?
• Jul 09, 2012 : What can cause TRUNCATE TABLE to take a really long time?

Further Suggestion : Based on a post from mysqlperformanceblog, you should get Percona Server or upgrade to the latest version of MySQL (5.5.23 has improvements to DROP TABLE, so 5.6 should have those improvements as well).

As for your current DROP TABLE, you must let it ride for maintain InnoDB's stability.