WooCommerce Performance: Indexing the post_modified_gmt column

WooCommerce 5.8 added support for `modified_before` and `modified_after` params when querying to the REST API for products, orders and coupons in the in the REST API endpoints. Here’s the PR that was merged.

This is great as lots of external services use the REST API to fetch data, and this allows them to just fetch data that has changed since the last sync.

However, if you have a really large posts table (~1 million records and up), this type of query may be slower than you’d like as `post_modified` and `post_modified_gmt` are not indexed columns in the database.

Continue reading

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.

Continue reading