Magento: Easily and Quickly Delete All Products
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.
That sounds about right.
Do you suppose there is an equivalent for deleting all orders? I want to get rid of my test data for orders, but keep my products intact. I’m not sure which table has the data.
I assume there would be something similar – I just haven’t had to test it yet.
Nice one, thank you, thought I was gonna be here all day!
Thank you for this! I had an error where everything was deleted in the back end but products were still showing up in the front end. This 1 line solved the problem! Thanks again!
actually this is a bad way to delete all your products. There will be orphaned database rows in other tables and will clutter up your database. You need to delete all traces of products in all tables in the database.
catalog_category_product for example
@j: Agreed. Not the correct way to do it. Several tables missing.
I posted the solution back on the forum:
http://www.magentocommerce.com/boards/viewthread/20553/
“j” is right. it’s a bad way to do it. at some point you’ll get integrity constraint errors because of the orphaned rows in other tabled.
@j,Mike,x: I disagree. It’s true that best practice says editing the db directly is a bad idea, but as noted in this post, Mage has the foreign keys set to cascade. If they configured all the foreign keys properly and comprehensively (as any good db architect should), then deleting a row from catalog_product_entity will also delete its corresponding reference in catalog_category_product (or disallow the original row from being deleted if using “restrict”).
what about product images in media/catalog/products/ directory?
should i remove them manually?
This works really well.
Thanks
Ian
EasyCaribbeanShop.com