HPOS Full-text search for orders is available in experimental mode

vedjain Avatar
vedjain

•

We are excited to announce availability of Full-Text Search (FTS) indexes in experimental mode starting with WooCommerce 9.0, to be released June 11, 2024. In HPOS, we moved order addresses to a separate table, and now, we support adding full-text search indexes to order addresses and order products.

When enabled, all the order searches will be automatically redirected to full-text search indexes. Additionally, we have also made the non-FTS search faster when using HPOS.

Benchmarking

In our internal testing, this index has been quite promising. On a test site on WordPress.com’s $25/month plan with 500k orders and 30k products, (numbers are the average of the total time taken by search queries across 5 requests):

SearchWooCommerce 8.9WooCommerce 9.0WooCommerce 9.0 with Full-text indexes
All (no results)3261ms1904ms676ms
All (3k+ results)6679ms3070ms547ms
Customers (no results)1049ms1033ms0.6ms
Customers (3k+ results)2111ms2119ms40ms
Products (no results)1897ms781ms0.6ms
Products (3k+ results)3715ms1507ms53ms

Benchmarking of WooCommerce order search

Known Quirks

We’re labeling MySQL’s FTS index search as experimental for now, as it is a relatively new addition that is not yet extensively documented for the WooCommerce ecosystem. Our goal is to make it stable and automatically available for stores using HPOS in a future update once we have addressed some of the following quirks present in the system today: 

  1. Inconsistency in search/tokenization: The FTS index works by tokenizing the content based on token size as defined by several MySQL config params (innodb_ft_min_token_size, innodb_ft_max_token_size, etc). Right now, we are not specifying any param in our implementation, which means it will use whatever is the configured default for the shop. This may induce inconsistencies such as minimum character length (default 3), token-only search, etc. We plan to settle on the configuration that will work for most shops while the feature is in experimental mode.
  2. Server support: While the FTS indexes are supported from MySQL 5.0+ onwards, we found in our testing that they didn’t work for versions from 8.0.29 to 8.0.35. We expect some other inconsistencies across the version range and MariaDB implementation.
  3. n-gram configuration: We are currently using the built-in language parser instead of the dedicated n-gram parser, which may support more comprehensive search at the cost of more space. However, if the default parser does not work as expected, we plan to switch to the n-gram implementation.

Enabling the feature

To enable the feature, make sure you have WooCommerce 9.0 or enabled. Use the WooCommerce Beta Tester plugin or download the latest beta release from WordPress.org to get the current beta for WooCommerce 9.0. 

Next, go to WooCommerce > Settings > Advanced > Features and enable the HPOS Full-text search indexes option. Note that this feature only works when Order data storage is set to HPOS. This setting is also forward compatible, so you can disable/enable it as needed:

Feedback

We highly recommend testing out this feature on your staging environment. We’ve kicked off a GitHub Discussion for this topic, and we look forward to learning about your experience with this feature and any feedback you share with us.

Keep yourself in the loop!

Sign up for the WooCommerce developer newsletter:
Hidden
Hidden
Hidden

6 responses to “HPOS Full-text search for orders is available in experimental mode”

  1. Kathy Avatar
    Kathy

    Hi Vedanshu! Is there an example of how to use this?

    Reply
    1. vedjain Avatar
      vedjain

      Hey, yes, so when you enable them, the search queries will change to use the FTS index by default. For example, on an HPOS enabled shop: go to WooCommerce > Orders and then searching using some search term. You would also better results when using the Customers or Products filters instead of a wider search. If you are using a query monitor plugin, then check for presence of MATCH….AGAINST syntax in the query log to confirm if the FTS indexes are being used.

      When FTS are in use, you would see query logs like this:

      https://developer.woocommerce.com/wp-content/uploads/sites/2/2024/06/Screenshot-2024-06-18-at-3.40.57%E2%80%AFPM.png

      For comparison, these are the logs without using FTS indexes:

      https://developer.woocommerce.com/wp-content/uploads/sites/2/2024/06/Screenshot-2024-06-18-at-3.44.37 PM.png

      To use these in code, first check whether the FTS indexes are present in the site using option woocommerce_hpos_address_fts_index_created (for wp_wc_order_address) and woocommerce_hpos_order_item_fts_index_created (for wp_woocommerce_order_items) and checking if their value is set to yes. When yes, you can use the FTS query syntax, see: https://dev.mysql.com/doc/refman/8.4/en/fulltext-search.html, otherwise use the regular SQL search query which will continue to work regardless of FTS option.

      Reply
  2. Lou Avatar
    Lou

    I’ve been testing it on a demo store. Firstly, the search is incredibly fast; good work on this front. This is exactly what we store owners need more than front-end FSE development. Please continue pushing for more backend performance gains.

    — On the the bug —

    I’ve discovered a bug when searching for an order by a specific product. When I paste the product’s name in the search bar, for example, “Casual T-Shirt Blue XL,” instead of getting all orders with that specific product, I receive all orders containing any of the individual words. As a result, I end up with orders that just have a random T-shirt or other unrelated items. Is it possible to fix this issue?

    Reply
    1. vedjain Avatar
      vedjain

      Thanks, good to know that you are finding the search fast!

      When I paste the product’s name in the search bar, for example, “Casual T-Shirt Blue XL,” instead of getting all orders with that specific product, I receive all orders containing any of the individual words.

      So our current implementation do not support searching for all terms yet, but we can add support for it! It is infact a change from previous search, as such I would consider it a bug.

      BTW, we are discussing this in GitHub Discussion for this topic as well, feel free to join us there!

      Reply
  3. Bill Avatar
    Bill

    AFAIK this doesn’t include SKU’s yet? This would be a great feature to include

    Reply
    1. vedjain Avatar
      vedjain

      Correct, currently it does not include SKU (or other product metadata) and is limited to product name. Thanks for feedback, let’s see if we can include it.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *