Table of Contents
WooCommerce provides two easy options, such as ‘Trash’ and ‘Delete Permanently’, to manage orders. However, as your store grows, you must consider optimizing your database. While trashing or deleting an order permanently may seem straightforward, it’s important to understand what happens in the database and the real impact of these actions on your site’s performance when an order is deleted in WooCommerce.
When an Order Is Moved to Trash
When you move an order to trash in WooCommerce, it will just temporarily hide the order from the admin and in the front end. But this action doesn’t delete the order data from the database. Let me show you the database still has the order data of the orders moved to the trash.
Technically, moving an order to trash alters the post_status field of the associated order record in the wp_posts table. Specifically, the post_status is changed to “trash” from the standard “completed,” “processing,” or other active statuses.
Note: The SQL query will be different if you are using the legacy database structure.
When an Order is Deleted Permanently
Let’s now see what happens when an order is deleted permanently, which creates an impact on the performance of the database. When an order is permanently deleted, it undergoes an irreversible change compared to simply moving an order to the trash.
There’s a misconception that deleting an order automatically improves site speed by removing all related data. However, this is not the case. Let’s explore what really happens when an order is deleted permanently and check the database to ensure that all related order data has been removed.
If you are using HPOS (High-Performance Order Storage) introduced in WooCommerce 7.1, order data is stored across multiple specialized tables for better performance. The deletion of an order from HPOS will involve removing data from primary HPOS orders tables like wp_wc_orders, wp_wc_orders_meta, wp_wc_order_items, wp_wc_order_stats, etc.
But some related data (such as order notes, analytics stats, and custom data) may remain in certain tables like wp_comments, wp_wc_order_stats as WooCommerce doesn’t do a full cleanup of data across all tables, especially if custom or third-party data is involved. In such scenarios, you need to manually delete the data from those specific tables using SQL queries.
For instance, you can see the below image where the comment table data of the particular order still remains intact in the database.
Use SQL Queries For Deleting Orders Based on Specific Conditions
As we have discussed till now, WooCommerce may still leave behind some data in various tables even when the order is deleted permanently. In such cases, to ensure that all related data is properly removed and to optimize the performance of your store, you can use SQL queries to delete order data based on specific conditions, such as order notes of a specific order ID.
The following SQL query can be used to delete the order notes from the wp_comments table for a specific order ID (2010), which has been permanently deleted, but its residual data still remains in the database.
DELETE FROM iwpc5bb_comments WHERE comment_type = 'order_note' AND comment_post_ID = 2010;
Best Practices for Optimizing WooCommerce Database
If you frequently delete orders from the admin and don’t know whether your database has removed all related data, your WooCommerce database can become bloated with unnecessary information. This can lead to slower performance, especially as your store grows.
To keep your store optimized:
- Regularly clean up old or irrelevant order data.
- Consider automating database maintenance using WordPress cron jobs to remove old orders.
- Implement indexing for high-performance queries, such as filtering by order_id or post_status.
- Always backup your database before performing any deletions or modifications.
Want to effortlessly impro database performance with just a simple click.
To make this process easier and more efficient, we’re excited to introduce our Auto Archive Orders For WooCommerce, designed to automatically archive old orders, remove unnecessary data, and keep your WooCommerce store optimized. Take a look at how the plugin works with these introductory videos.
Check out the Demo of Auto Archive Orders Tool for WooCommerce video!
Want to be notified when it’s released? Leave a comment below, and we’ll let you know as soon as it’s available!
