Fast Woo Order Lookup

PMF

What’s the background for this?

See this WooCommerce issue for an example of the performance problem store owners have. See this Subscriptions issue for another example.

What’s the fix?

Build a trigram lookup table, maintain it, and use it for the queries.

How much space does the lookup table — `wp_fwol` — take?

It takes about 5-10KiB per order, as MariaDB / MySQL database storage, counting both data and indexes. So, if your site has a million orders, the table will take something like 5-10 GiB. The rows of the table are each quite small, just three letters and an order ID. And there are many of those rows for each order.

The table, named with an abbreviation for “Fast Woo Order Lookup”, contains the trigram lookups. It has a terse name to keep queries short. It is dropped automatically if you deactivate the plugin.

Can it get so large this plugin becomes useless or counterproductive?

No, unless your database tablespace is too small for it.

This answer uses the Big O conceptual way of understanding program performance.

The table is organized by its primary key so this plugin can search for orders with O(log n) computational complexity. That means if searching 100 orders takes two seconds, then searching 1000 takes about three seconds and 10,000 about four. So it will work at large scale. And without this plugin the complexity of the order search in WooCommerce is a quite unpleasant O(n). Ten times as many orders take ten times as long to search. So, 100 times as many take 100 times as long. Used at large scale that gets nasty. It burns server electricity. Just as importantly, it wastes users’ time.

That’s true even if you use a nice search plugin like Relevanssi to help your customers search for products. The author does that. It works. But not on orders.

This plugin improves order search performance by using a better algorithm. It’s the InnoDB team we have to thank for this in MariaDB and MySQL. Legendary. (See, Dr. Knuth? Somebody read your book!)

If your hosting service is such a cheapskate you don’t have the tablespace for the table, that might be a reason to avoid this plugin.

How long does it take to generate trigram lookup table?

When you activate the plugin, it starts generating the table in the background. Everything continues as normal while the plugin is generating the table.

Generating the table seems to take about ten seconds (in the background) for every thousand orders.

Does it work with High Performance Order Storage (HPOS)?

Yes. It also works with the performance enhancements in WooCommerce 9.9.0 and beyond. Alas, it is still required to get good performance with order search.

Does it work with pre-HPOS order storage?

Yes.

The lookup table seems to be out of date. I can’t find recent orders. What do I do?

1. Let the author know by creating an issue at https://github.com/OllieJones/fast-woo-order-lookup/issues
2. Deactivate, then activate the plugin. This rebuilds the lookup table.

My store only has a few hundred orders. Do I need this plugin ?

This plugin addresses database performance problems that only show themselves on stores with many thousands of orders. If your store is smaller than that you probably don’t need it.

Wise owners of rapidly growing stores return regularly to examine their site performance. If your site is still small, it’s better to wait until you actually need performance-enhancing plugins and other features. Installing them “just in case” is ineffective.