Thursday, May 19th, 2022

WooCommerce Performance: Indexing the post_modified_gmt column

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

This is great because a lot of external services use REST APIs to fetch data, and this allows them to only get data that has changed since the last sync.

However, if you have a really large posts table (~1 million records and above), this type of query may be slower than you expect because `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 lots 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 

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;

Source link