Slow Truncating InnoDB Tables that have Foreign Keys set on them

One of the advantages of using InnoDB as your storage engine in MySQL is that you can create a database structure that will automatically keep itself clean and tidy, by having deletes cascade through your tables as soon as you delete the main entity.

However, if you need to empty a large table, you will find that this process is really slow.

The solution is to first of all truncate all of the tables that have foreign key relations set on the main table and then only empty the main table once those child tables are emptied.

Yes this defeats the object a bit, but if you want speed, this is the solution. It is incredibly fast in comparison, and the end result is the same.

More Reading:

• Failing to Create Foreign Keys (errno 150) + Solution
• Yii Setting up Authorisation – the Missing Manual
• MySQL Performance Tuning
• Magento admin backend slow (quick speed tip)
• Very High Speed Batch Update Multiple Rows of a Table in Single Query
• The user specified as a definer (‘something’@'somewhere’) does not exist +SOLVED
• Magento Hosting Optimisation Tips
• Magento Optimised Hosting
• Using PHP to Stream SQL results dynamically as CSV, even to IE
• MySQL Got Error 28 From Storage Engine + Solution

 

Leave a Reply

Your Name *

Your E-mail * (will not be published)

Website

Your Message