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
Best Answer
Perhaps you can disable foreign key checks and unique checks:
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 / reloadMay 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.