Improving API Queries for Low Stock Products

tl;dr

Recent improvements to the REST API fix slow queries for products with low stock. There is a new REST endpoint that affected developers should adopt.

The Details

There was a recent issue in WooCommerce Admin where stores who had upgraded to WooCommerce 5.5 started to experience performance issues and, in some cases, stopped working altogether. Community members were quick to jump in with helpful workarounds and alternatives while the team of folks who maintain WooCommerce Admin investigated the issue.

The root cause of the issue was twofold:

1. A REST API endpoint was using an inefficient SQL query to retrieve products that were low in stock.
2. WooCommerce 5.5 calls this particular endpoint more frequently than previous releases of WooCommerce do.

A full fix was released in WooCommerce Admin 2.4.4, which was bundled into WooCommerce 5.5.2.

This fix brings some noticeable performance improvements to the query that retrieves low stock products. As an example, in a store with about 10,000 products, the original query exceeds 30 seconds and times out. The new query executes in around 200 milliseconds, on average. You can see the full fix and read more details in the associated pull request on GitHub.

As part of these improvements, a new, dedicated endpoint for querying low-stock products landed in the WooCommerce REST API. This endpoint is available at /wc-analytics/products/low-in-stock/ and is meant to supersede the existing method of querying low stock products via /products?low_in_stock=true. With that in mind, WooCommerce Admin has been updated to make use of the new endpoint, and developers who currently use the existing query-string method are strongly encouraged to adopt the new endpoint. The query-string method is still currently active, but developers will notice that the low_in_stock argument has been marked for deprecation in the API’s routes. While the full deprecation of the old method has not occurred yet, the team maintaining WooCommerce plans to move forward with the deprecation in an upcoming sprint.

How can I tell if this affects me?

This may affect you if any of the following are true:

• You have a store with a large number of products and you are running a release of WooCommerce prior to 5.5.2.
• You are a developer and you are using the /wc-analytics/products?low_in_stock=true endpoint to query low stock products.
• You are a developer and you are using the /wc-analytics/products?low_in_stock=false endpoint to query products that are not low in stock.

What action should I take?

If you are a merchant, we recommend updating to the latest release of WooCommerce, which contains the fix for this issue.

If you are a developer, we recommend auditing your extension for any usage of the old endpoint and updating your code to use the new dedicated endpoint. This will not only ensure your code continues to function for merchants, but will also help you improve the performance of merchant stores by taking advantage of the new optimized query.

There are some instances, however, where adopting the new endpoint might not be a straightforward process. For example:

• The new endpoint accepts a smaller set of parameters than the /wc-analytics/products endpoint accepts, which may present challenges if you rely on query parameters that the new endpoint does not support.
• The new endpoint also does not support queries for products that are not low in stock (i.e. the equivalent of low_in_stock=false).

With that in mind, if you are using the existing /wc-analytics/products endpoint with the low_in_stock parameter, we’d love to hear more about how you’re using it, because it will help the WooCommerce Admin team plan their next steps.

As always, if you have questions or concerns about these updates, please don’t hesitate to drop them in the comments below or reach out to us in the WooCommerce Community Slack.