Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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

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.

share|improve this answer
    
wow I wish I had more reputation to upvote your answer. Thank you very much! –  EnJayz Nov 18 '13 at 23:58
    
Thank you for accepting, and Welcome to the DBA StackExchange. –  RolandoMySQLDBA Nov 19 '13 at 0:00
    
thank you, sir. –  EnJayz Nov 19 '13 at 0:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.