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.

Here’s a query we were doing that took ~25 seconds to run against our database (with a LOT of post records):

SELECT
	ID,
	post_modified_gmt
FROM
	wp_posts
WHERE
	post_type = 'shop_order'
	AND post_status NOT IN ('trash', 'draft', 'wc-pending')
	AND post_modified_gmt >= '2021-10-08 16:59:01'
	AND post_modified_gmt <= '2021-10-08 17:10:01'
LIMIT 0,
10000;

After we indexed this column, the query took ~0.05 seconds. An enormous performance improvement!

There aren’t any downsides to indexing this column that we’ve found yet, other than the size of the database increasing slightly to accomodate the additional index data.

To index this table, you’d run something like this:

ALTER TABLE wp_posts
ADD INDEX post_modified_gmt (post_modified_gmt) USING BTREE;

Leave a Reply