Slow searching for WooCommerce orders

Background

WooCommerce offers a search box on the WooCommerce -> Orders page. It lets a shop manager search orders, current and historic, for the customer’s name, address, email, and other data. It also searches the names of products and shows orders containing those products. On large sites with many orders, the search is astoundingly slow. One large site has reported search times of half a minute or more. That is unacceptable performance in a web app.

The same is true of the Subscriptions add-on plugin, on the WooCommerce -> Subscriptions page.

Why? These searches use unanchored text searches. For example, if a customer’s email is 'info@plumislandmedia.net' and somebody searches for 'plumisland' the search will find that customer’s orders. It does this using a notoriously slow SQL idiom: value LIKE '%plumisland%’. That idiom is the way SQL searches for the text string anywhere within the value.

A fix for the poor performance is to use anchored text searches. They use the similar, but much faster, SQL idiom value LIKE 'plumisland%’, without the leading % wildcard character. This allows the search to exploit a database index. There’s a tradeoff, however. An anchored text search for 'plumisland' won’t find orders from our example customer, because the customer’s email does not start with the search term.

Searching for 'info@plum', the first few characters of the email, will locate our customer in both anchored and unanchored searches.

WordPress plugin

This article presents a WordPress extension that changes the order search to anchored.

Here’s the source code of the plugin. You can get a downloadable and installable .zip file here.

For best performance results, add a compound covering index to WooCommerce’s `wp_woocommerce_order_items` table, like this.

ALTER TABLE wp_woocommerce_order_items 
    ADD KEY items_id (order_item_name(128), order_id);

As of late November 2023 it’s very simple. It has no configuration options. And, as of November 2023 it isn’t in the plugin repository yet.

It works by filtering and editing the specific MySQL / MariaDB queries.