As the number of orders in your WooCommerce site grows, so will the database size. At Universal Yums, our database size is 120GB and still growing!
The three tables that tend to be the largest are postmeta, comments (order notes), and woocommerce_order_itemmeta.
Check Your Database Size
To check your database size, you can use the WP CLI command:
wp db size --tables --human-readable
Or you could do a direct MySQL query:
SELECT table_name, ROUND((data_length + index_length) / POWER(1024, 2), 0) AS tablesize_mb FROM information_schema.tables WHERE table_schema = 'yourdatabasename' ORDER BY tablesize_mb DESC LIMIT 20;
The best way to reduce the database size is to remove data that you no longer need on the site. For example, our payment processor adds ~10 meta records for each entry. Some of this meta we want to retain long term (transaction id, payment method, etc) but the rest is just used for debugging and can be deleted after 90 days.
To see what kind of data gets captured on each order, I recommend installing the Post Meta Inspector plugin. A SQL query will also be really helpful to understand how much data is being stored for each meta key.
SELECT meta_key, count(*) AS count FROM wp_postmeta WHERE post_id < 100000 GROUP BY meta_key HAVING COUNT(*) > 1000 ORDER BY COUNT(*) DESC;
One type of meta data that’s generally safe to delete from orders is “_customer_user_agent” and “_customer_ip_address”. After 90 days most companies have no use for this data. It can be removed with a SQL query like this:
DELETE FROM wp_postmeta WHERE meta_key = '_customer_user_agent' AND post_id < 100000 LIMIT 10000;
Direct SQL queries are generally the quickest way to go, especially if a site hasn’t had any clean up previously (make your backup first!). However, once you’ve tidied up the database, you might want to run a script every few months to cleanse data from newer orders.
Here’s an example WP CLI script, similar to what we use at Universal Yums, that deletes specific meta data from completed orders that are more than 90 days old. Feel free to adapt it for your own uses!
Since the postmeta table tends to be the biggest and can impact site query times the most, that’s generally where I’d start. However, truncating log tables (actionscheduler_logs, etc.) or deleting tables added by plugins that are no longer used on the site can also be quick easy wins.
If you deleted a lot of data, but the database size is not any smaller, you likely need to OPTIMIZE the table. You can read these notes about InnoDB. The only problem is this locks the table and can take a VERY long time to run.