If you have ever tried to delete a large amount of products out of Magento, you’ll realize that it takes forever, and in many cases, you have to do it in small quantities at a time otherwise it will freeze up. I needed to delete all the products out today but found that I could only delete about 30 at a time. This just wasn’t going to work since there are about 2500 products in the DB. I found something out today by accident that turned out to be extremely helpful.
Due to the nature of the InnoDB mysql engine that Magento uses, and the way they setup their foreign keys, all it takes is one simple mysql command, and mysql will automatically clear out all of the product data from all of the different tables it uses. On top of that, it resets the auto-increment values back to 1 so that you can start off with entity_id 1 again.
I haven’t given it extensive testing, but I checked every table that I knew that stored product information and they were all clear. So, if you need to clear out all your products, open up mysql and just enter this one line in:
TRUNCATE TABLE `catalog_product_entity`;
For more information on how it works, you can visit the Truncate Syntax page on the Mysql website.