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;