Fast Woo Order Lookup

Description

WooCommerce’s Order and Subscription pages allow store owners to search for orders and subscriptions by customer name, email, and other attributes. By default, it does a general substring search. For example, if you put OllieJones into the search box, it will search with LIKE '%OllieJones%' using the leading wildcard %. That’s astonishingly slow on sites with many orders.

Upon activation this plugin runs a background process to create a special-purpose index table, a table of trigrams, to speed up that search. Then it uses those trigrams to search for orders.

The downside: the trigram table takes database space and takes time to generate.

The orders page itself contains a very slow query (to be fixed in Woocommerce 9.0.0) to look up meta_keys. This fixes that query’s performance too.

Credits

Thanks to Leho Kraav for bringing this problem to my attention.

Thanks to Jetbrains for the use of their software development tools, especially PhpStorm. It’s hard to imagine how a plugin like this one could be developed without PhpStorm’s tools for exploring epic code bases like WordPress’s.

How can I learn more about making my WordPress site more efficient?

We offer several plugins to help with your site’s database efficiency. You can read about them here.

Installation

  1. Go to Plugins in the Admin menu
  2. Click on the button Add new
  3. Click on Upload Plugin
  4. Find fast-woo-order-lookup.zip and upload it

FAQ

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 trigram lookup table take?

It takes about 5KiB 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 5GiB.

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.

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.

Reviews

There are no reviews for this plugin.

Contributors and Developers

“Fast Woo Order Lookup” is open source software. The following people have contributed to this plugin.

Contributors

Change Log

= 0.4.1 June 15, 2024

  • Make the patch for slow order-page viewing compatible with WooCommerce 8.9.3.
  • Fix a presentation defect in the table-generation notify message.

0.4.0 May 10, 2024

Patch the query to look up distinct public meta_key values.

0.3.0 April 25, 2024

Use JOINs rather than IN to get better performance. Shorten the table and column names.

0.2.6 April 15, 2024

Notice, localization, phpcs:ignore

0.2.5 April 13, 2024

Background loading. Correct handling of HPOS variant queries (from the dropdown).

0.2.4 April 6, 2024

Ingest wp_wc_order_addresses info when creating trigram table, and handle pre-HPOS sites correctly.

0.2.2 April 1, 2024

Perform trigram inserts in batches.

0.2.1 March 26, 2024

Keep up with changes to orders.

= 0.1.4 March 23, 2024

Use trigrams, support both traditional and HPOS orders.

= 0.1.3 March 21, 2024

Build a text index table and use it.

= 0.1.2 November 24, 2023

Add support for speeding Subscriptions searches.

= 0.1.1 November 19, 2023

Birthday of Fast Woo Order Lookup. And, the birthday (in 1988) of the author’s daughter Catharine.