Cleanly Delete all Orders, Sales & Customer Data in Magento

Geoff Jackson

Geoff Jackson (more commonly known by his online pseudonym zigojacko) is the founder of Design Haven and The Clubnet Group which hosts numerous agencies including the most widely recognised of them, Clubnet Digital, a full service digital marketing and creative design/development agency. He also has a personal blog but it doesn't get updated as much as he'd like.

You may also like...

Sponsored Links...

Subscribe to Design Haven UK

Enter your email address to subscribe and receive our new posts by email.

27 Responses

  1. Nick says:

    Great list, thank you! I also wanted the search terms cleared out from the dashboard, so I ran the following in addition:

    TRUNCATE `catalogsearch_query`;
    TRUNCATE `catalogsearch_fulltext`;
    TRUNCATE `catalogsearch_result`;
    ALTER TABLE `catalogsearch_query` AUTO_INCREMENT=1;
    ALTER TABLE `catalogsearch_fulltext` AUTO_INCREMENT=1;
    ALTER TABLE `catalogsearch_result` AUTO_INCREMENT=1;

    TRUNCATE TABLE `sales_bestsellers_aggregated_daily`;
    TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;
    TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;
    ALTER TABLE `sales_bestsellers_aggregated_daily` AUTO_INCREMENT=1;
    ALTER TABLE `sales_bestsellers_aggregated_monthly` AUTO_INCREMENT=1;
    ALTER TABLE `sales_bestsellers_aggregated_yearly` AUTO_INCREMENT=1;

    • Thanks for the comment Nick, yes, you’re suggestion code would be useful to clear out any of the data in the search terms and bestsellers, thanks for providing the code for this.

  2. Tony Jackson says:

    Many thanks for this – works a treat, this has solved a real headache for us.

  3. n says:

    Thank you for the great work.
    One question, I run this SQL script plus the comment from Nick in Magento and it worked fine but then in few minutes later slowly started to update the “Search Terms” and few “Products in carts” that was from before.When I first installed the theme to Magento I had to import some files including test orders and etc from PhpMyAdmin.

    • Hey,

      Sounds like you may have some SQL script that re-ran from when you imported the dummy data initially which re-imported the search terms data back in to your database again?

      Hard to say to be honest without knowing your exact set up, configuration etc.

      Perhaps post the question specifying all details to Magento SE and there’ll be someone that will be able to discuss further and shed some light on this for you most likely.

      Thanks for stopping by and taking the time to comment with your results post actioning this query.

  4. Diego Castro says:

    Thank you, worked like a charm!

  5. Kátia says:

    Can I use this to Magento or I need to run another command?

  6. Ink Factory says:

    Thanks for this – we’ve used this in our work flow to quickly deploy a staging server which exactly mirrors our live site, minus all the customer data. Works a treat on Magento

    Cheers, Tim.

    • Good stuff Tim, glad you found it useful.

      • Ink Factory says:

        Also, you might want to include this, which clears the newsletter subscribers too:


        TRUNCATE newsletter_problem;
        TRUNCATE newsletter_queue;
        TRUNCATE newsletter_queue_link;
        TRUNCATE newsletter_queue_store_link;
        TRUNCATE newsletter_subscriber;
        TRUNCATE newsletter_template;

        ALTER TABLE newsletter_problem AUTO_INCREMENT=1;
        ALTER TABLE newsletter_queue AUTO_INCREMENT=1;
        ALTER TABLE newsletter_queue_link AUTO_INCREMENT=1;
        ALTER TABLE newsletter_queue_store_link AUTO_INCREMENT=1;
        ALTER TABLE newsletter_subscriber AUTO_INCREMENT=1;
        ALTER TABLE newsletter_template AUTO_INCREMENT=1;


        • Thanks for the contribution Tim, indeed, this could also prove useful for clearing our test/dummy data. Will see if we can get the post updated with this too. Thanks.

  7. Maria says:

    Thank you sharing your experience on how to tackle problem in database. Those who do not have any idea about running that queries, can remove orders free downloading Store Manager for Magento version

    • Thank you for stopping by and leaving a comment Maria but I cannot recommend Store Manager for Magento in the slightest. It is buggy, not very user friendly, causes countless issues and is incredibly overpriced for the hassle that the software causes.

      We’ve numerous clients that can vouch for that feedback.

      It’s a shame that in the 3+ years I know of that Store Manager has been available on the market, it has not improved and rectified these bugs/glitches.

      • Maria says:

        Hello Geoff. Even Magento itself has bugs. The question is how quickly they are solved and what the features are included. We are always trying to improve Store Manager for Magento and eliminate the issues occurring. We are interested in improving the app on the basis your feedback, so if you remember what issues you or your clients encountered, we can provide you free copy for the pointing us in the right direction. If you are interested, e-mail us to

  8. K Khan says:

    it’s not working on

    please help! 🙂

  9. hykont says:

    Jackson you are the best, and every other person that contributed so far to this… You really safe me with the script..much love

  10. James says:

    Many thanks !

    But I also want to share an available Magento extension to help delete orders quickly and easily

    Hope it is also useful!

  11. Jonathan says:

    Thank for great infor!
    For amateur user who not familiar with coding process, I suggest a great extension that work well on both Magento 1 and 2. Managing orders in backend becomes easier than ever with this helpful tool!

    For Magento 1:
    For Magento 2:

    • Thanks for suggesting these extensions Jonathan. I have no experience of using these extensions so cannot vouch for them or comment.

      For the benefit of other visitors though, the recommended extensions by Jonathan only deletes orders whereas the above SQL does a lot more. As always, approach any third party extensions with caution and take full backups as always before first use.

  12. Roberto says:

    Thank you very much, it has served me and it is very useful.

    I need if it is possible to use the same table deletion but in a certain date range; that is, I need to use those same queries but to erase all data prior to 2017

    Is this possible to do ?

    Thank you very much in advance

    • Hey Roberto,

      That won’t be easy with SQL queries as there are many tables referenced above they use relationships with other tables (not all having a column for date/time inserted).

      So whilst for some columns, you could create individual queries, it would leave a lot of zombie data in the database still where you can’t see when the records were created.

      Instead, you’d be better off to write a script which you can run which will iterate through whatever you set and delete the records accordingly associated to it.

      For example, here would be a good starting point.

      Hope that helps. And good luck!

  1. May 22, 2016

    […] may need to clear out all customer and order information. Kudos to Geoff Jackson for this solution . I’ve used this a few times now and want to include it here for easy […]

Leave a Reply to Tony Jackson Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: