Fast Woo Order Lookup

This is a plugin for big WooCommerce sites. It speeds up search on the Order page in the dashboard. It mitigates the performance issues in this WooCommerce issue and this Subscriptions issue. It also mitigates a performance issue in this issue.

Big news (yeah, ok, sorta-big news) as of July, 2024: You can get version 1.0.1 of plugin from the plugin repo here. /fast-woo-order-lookup/ You can install and activate it the usual ways.

The source code is at https://github.com/OllieJones/fast-woo-order-lookup . You can offer suggestions or report bugs at https://github.com/OllieJones/fast-woo-order-lookup/issues .

Custom Field Name Cache

The plugin provides a cache to fix a serious performance regression on WooCommerce’s Edit Order page (the one you get when you click on an order from the WooCommerce -> Orders list orders). As of 8.9.0 the HPOS version of that page contains a pulldown list of the names of custom fields. The WooCommerce code retrieves that list by scraping it out of the wp_wc_ordermeta table. If your store has accepted many orders (congratulations! that’s a big deal to have many orders) that table has many rows. And the custom-field-name scraping query has to look at most of them. It’s slow. So this plugin caches the result of that query in a transient and doesn’t repeat it.

Order Search Box Speedup

It makes the search box on the WooCommerce -> Orders page actually return results before it times out. All performance-snob snark aside, seriously, that Search box needs to do some really challenging MariaDB (or MySQL) queries to deliver the results store operators need. That search box looks for the string they type all over the historical orders in their system: Customer name, full text address, names of products in the owner, email addresses, even payment gateway transaction numbers. And to serve their customers and their businesses, they need to be able to find orders reliably. This search box looks in a lot of places to find matching orders, even when it is given only a few characters to search.

And that is a complex query, with multiple meta_value LIKE '%searchterm%' search predicates in it. Those predicates are the DBMS archetype of stupid slow query. And these product searches use them. Because store owners need to be able to find orders. (Notice that the PostgreSQL DBMS offers trigram indexes, so if WordPress used it this plugin wouldn’t be needed. )

This plugin deals with that performance problem by hooking into WordPress’s database query system, intercepting those queries and adding an extra predicate to each query:

WHERE order_id IN(a list of ids that have matching text strings) AND ...

And the point of this plugin is to efficiently and quickly retrieve that list of ids.

How does it work? Trigrams.

What’s a trigram? It’s a chunk of three consecutive letters from a text string. For example, the first trigram in the string “Beatles” is “Bea”. The second one is “eat”, and so forth. They are useful in database work because SQL databases handle tables with a lot of short rows efficiently. It’s much cheaper to satisfy a query saying trigram = 'Bea' than it is meta_value LIKE '%Bea%'.

This plugin makes a table of trigrams in the searchable text that describes each WooCommerce order. For example, if order # 123 is for a record called The Beatles! these entries appear in the trigram table.

As the example shows, every consecutive pair of three letters — every trigram — in the text string gets its own row in this table. For a lot of orders, each with a lot of metadata, that turns into many table rows. But they are short and efficiently indexable rows — a 64-bit integer and three Unicode characters. In fact the primary key of the table is the composite of the table’s only two columns (trigram, order_id). That means the entire content of the table is in the primary key. So searches such as

SELECT order_id FROM trigram
 WHERE trigam = 'Bea'

are satisfied by one of the fastest ways to access a BTREE table: an index lookup.

To search for the text string “Beatle” in the table, the actual query searches the table for trigrams in the search term with a query like this.

 SELECT a.order_id
   FROM (SELECT order_id FROM trigram WHERE trigram = 'Bea') a
   JOIN (SELECT order_id FROM trigram WHERE trigram = 'eat') b ON a.order_id = b.order_id
   JOIN (SELECT order_id FROM trigram WHERE trigram = 'atl') c ON a.order_id = c.order_id
   JOIN (SELECT order_id FROM trigram WHERE trigram = 'tle') d ON a.order_id = d.order_id

This search contains four trigrams, and the query gets all the order_ids which have all four trigrams in them. That set of order_ids is guaranteed, by matching all four trigrams, to fetch all the matching ids. It may contain or may not contain some other order_ids, but it will definitely contain the ones we want. And, because of the marvellous SQL voodoo of the index lookups the query is fast, O(log n) fast, at finding that set of order_ids. That’s faster than the O(n) query built in to WooCommerce itself using multiple meta_value LIKE '%Beatle%' search terms.

To make sure we get the correct results, we’ll continue using the meta_value LIKE '%Beatle%' — riddled query, but we’ll include the extra

WHERE order_id IN(a list of ids that have matching text strings) AND ...

search predicate into the query to rule out almost all the orders before the slow searches.

Maintaining the trigram table

This trigram table must be created and maintained. It is not part of core WordPress or WooCommerce.

On activation and upon minor version upgrade (that is, an upgrade from 1.0.3 to 1.1.0, for example, not from 1.0.3 to 1.0.4) the plugin runs a background process via the ActionScheduler to create the table from existing orders in the system. Until the background process finishes order search works the old way.

The plugin sets up action handlers for order creation, updates, and deletions. Any order mentioned in any of those action handlers has its trigram list created, updated, or deleted as well, to keep it current. The same action handlers maintain the custom field name cache.

The plugin drops the trigram table when it is deactivated. Notice that the usual practice is to delete a plugin’s data when the plugin is deleted, not when it is deactivated. But, if the trigram table were preserved without being maintained, its content would no longer reflect the current orders. So this plugin drops the table on deactivation rather than letting it go stale. It would be be necessary to recreate it on reactivation anyway.

Where did this idea come from?

PostgreSQL has built in support for trigram indexes to support predicates such as meta_value LIKE '%Beatle%'. I don’t claim this plugin supports their precise algorithm. It does something similar, but from the application layer.

How Expensive Is This?

To the trigram table, each order in your system adds about 5KiB to tablespace storage in your MariaDB / MySQL database. So, if your WooCommerce store has a half million orders, the trigram table will use about 5GiB of database data (including both data and indexes). That is a modest amount of SSD / HDD storage for a store that size. You probably won’t have to pay your hosting service more money for this much extra storage.

It takes some CPU time on your WordPress site to generate the trigram table when you activate the plugin. Your site generates the table in the background. You can use order search while generation is in progress, but it will do the searches the old slow way until generation is done.

This plugin can save a huge amount of CPU time, tens of seconds per search. The generation of the table takes as much time as 10-20 searches, so it’s worth the trouble.

The plugin itself, like all plugins on the WordPress plugin repository, is free for you to download, use, study, and modify. The author invites you to file issues or send pull requests. Especially, if you know of other stupid slow searches anywhere in the WordPress ecosystem that don’t have a plugin to speed them up, please, let the author know.