WooCommerce Performance: Using post_author to store order customer IDs

WooCommerce stores order records in the `posts` table as a `shop_order` post type. The majority of data associated with the order, such as the order_total or billing and shipping information is stored in the `postmeta` table.

This works fine in most cases, but once a WooCommerce shop scales past ~1 million orders, queries of postmeta can start to run long. If WordPress needs to get a specific customer’s orders (such as in the customer account dashboard), it requires a querying against `_customer_user` key in the postmeta table.

To see how long it takes on your site, you can run a query like this (use an actual customer id). If it returns in less than as second, it’s probably not worth optimizing for at this point:

select * from wp_postmeta
where meta_key = '_customer_user' and meta_value = 10;

This post on the WooCommerce developer blog explains the performance issue well, and also has a proposed solution! Why not use the `post_author` column in the posts table to store the customer ID? Querying against a much smaller table against an indexed column is much faster (as that post describes).

Unfortunately this idea was implemented and then reverted out of WooCommerce core. The reason is that sometimes a shop admin will create an order for a customer, and if the post_author column is used to store the customer ID, then there’s no record of who actually created the order.

However, for the sites I work with, orders aren’t generally created by admins. In the rare cases they are, an order note will be added. So we’ll gladly take the performance improvements of storing the customer ID in the post_author column to make customer order queries more efficient.

In case you’d also like to make the switch, I’ve built a plugin you can use. Once it’s enabled, any future orders or subscriptions that are created or updated will save the `_customer_user` (ID) value to the post_author column in the posts table.

To update historic orders and subscriptions, you’ll need to run a script or wp cli command (I’m still working on that in the plugin).

The `_customer_user` meta will still be saved, so any queries that rely on it will continue to work. But, you can start to swap out those queries where needed to use the `post_author` column in the posts table instead.

Leave a Reply