gravatar

Edmonds Commerce are a UK based E-Commerce web development agency. We are based in Shipley which is in Yorkshire near Leeds and Bradford.

Our primary specialism is Magento and Magento2 and other PHP based E-Commerce platforms such as OpenCart.

In terms of technology we are very strong in PHP, MySQL, Linux, HTML, CSS and Javascript.

Slow Truncating InnoDB Tables that have Foreign Keys set on them

Aug 10, 2010 · 1 minute read
Category: mysql

This is post is now quite old and the the information it contains may be out of date or innacurate.

If you find any errors or have any suggestions to update the information please let us know or create a pull request on GitHub

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.


Tags: mysqlspeedinnodbforeign keystruncate