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;

Removing Data

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!

Other Tips

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.