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.

This entry was posted in Magento, MySQL. Bookmark the permalink.

16 Responses to Magento: Easily and Quickly Delete All Products

  1. Dj says:

    This has broken my web site. I’m using the latest Magento stable 1.6*.

    Anything to do with products and the page doesnt load now. I don’t know what to do.


  2. mlb jersey says:

    THX,but how to Delete categories

  3. sjolzy says:

    but i just want to delete some,not all, it take me a long time , but no idea

  4. Dan says:

    I would be careful with this. It appears all orders are also deleted.

  5. Sonassi says:

    You could always just just core functionality to do it, which will remove products cleanly – or it you are filtering down by attribute/category etc.

    We popped together a guide here:

  6. This works really well.



  7. brian says:

    what about product images in media/catalog/products/ directory?
    should i remove them manually?

  8. wyattisimo says:

    @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”).

  9. x says:

    “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.

  10. Mike says:

    @j: Agreed. Not the correct way to do it. Several tables missing.

    I posted the solution back on the forum:

  11. j says:

    catalog_category_product for example

  12. j says:

    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.

  13. Hammink says:

    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!

  14. Robin Card says:

    Nice one, thank you, thought I was gonna be here all day!

  15. Josh Pratt says:

    I assume there would be something similar – I just haven’t had to test it yet.

  16. Joy says:

    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.