WooCommerce HPOS Full-Text Search Indexes: From Experimental Toggle to Production-Ready Order Lookup

Why WooCommerce Order Search Gets Slow at Scale

Searching for an order by customer name or address on a store with 200k+ orders can take 8-15 seconds when WooCommerce falls back to LIKE ‘%term%’ queries against wp_postmeta or even the HPOS wp_wc_order_addresses table. The root cause is straightforward: LIKE with a leading wildcard forces a full table scan regardless of standard B-tree indexes.

HPOS moved order addresses into a dedicated table called wp_wc_order_addresses, separating them from the bloated wp_postmeta dump. That structural change alone cut query complexity, but it also opened the door for MySQL FULLTEXT indexes – something impossible on the old EAV schema where every address field was a separate meta row. Starting with WooCommerce 9.0, an experimental toggle ships that creates FULLTEXT indexes on both the address table and the order items table, redirecting all admin searches to use MATCH…AGAINST syntax instead of LIKE.

The performance difference is dramatic. On a benchmark with 500k orders hosted on a $25/month WordPress.com plan, the FTS-powered search returned results in roughly 0.2 seconds compared to 5+ seconds on the non-FTS path. Even the non-FTS HPOS search improved with WooCommerce 9.0 due to better index usage, but FTS takes it to a different level entirely.

Enabling FTS Indexes on Your Store

The activation path is intentionally simple.

Navigate to WooCommerce > Settings > Advanced > Features. Confirm that “Order data storage” points to High-Performance Order Storage (not legacy). Under the Experimental features section, check the box labeled “HPOS Full-text search indexes” and save. WooCommerce then creates two FULLTEXT indexes in the background: one on wp_wc_order_addresses covering address fields, and one on wp_woocommerce_order_items covering product names.

You can verify the indexes exist by checking two options in the database. The option woocommerce_hpos_address_fts_index_created should return “yes” for the address index. The option woocommerce_hpos_order_item_fts_index_created confirms the items index. If either returns “no” or does not exist, the index creation may have failed silently – check the MySQL error log for InnoDB FULLTEXT-related messages. A common cause is insufficient database permissions or a MySQL version that does not support the specific FULLTEXT syntax WooCommerce generates.

SELECT option_value FROM wp_options
WHERE option_name IN (
  'woocommerce_hpos_address_fts_index_created',
  'woocommerce_hpos_order_item_fts_index_created'
);

How MySQL FULLTEXT Indexes Actually Work

A FULLTEXT index builds an inverted index of tokens extracted from the target columns. Each token maps back to the rows containing it, which means lookups are O(1) hash-based rather than O(n) sequential scans.

MySQL’s InnoDB engine uses the built-in language parser by default. This parser splits text on whitespace and punctuation, applies a minimum token length filter (innodb_ft_min_token_size, default 3), removes stopwords from a built-in list, and stores the remaining tokens. When a MATCH…AGAINST query runs, the engine tokenizes the search input using the same rules and matches against the inverted index. Results get a relevance score based on term frequency, inverse document frequency, and field length normalization. The scoring means that a short address field with a strong match ranks higher than a long notes field with a weak match – exactly the behavior you want when searching for “John Smith” across 500k orders.

-- Example of what WooCommerce generates internally
SELECT o.id FROM wp_wc_orders o
INNER JOIN wp_wc_order_addresses a ON o.id = a.order_id
WHERE MATCH(a.first_name, a.last_name, a.city, a.address_1)
AGAINST('+john +smith' IN BOOLEAN MODE)
ORDER BY o.date_created_gmt DESC
LIMIT 25;

Boolean mode gives WooCommerce explicit control over which terms must be present (the + operator), which must be absent (-), and which are optional. Natural language mode ranks by relevance but cannot enforce mandatory terms, so boolean mode is the practical choice for admin search where precision matters more than fuzzy matching.

Tuning innodb_ft_min_token_size for Real-World Data

The default minimum token size of 3 characters works for most English text. It becomes a problem with WooCommerce order data because phone numbers, postal codes, and two-letter state or country codes are common search targets. A customer searching for orders shipped to “NY” or “CA” gets zero results because those tokens are below the 3-character threshold.

Changing this requires editing the MySQL server configuration file (my.cnf or my.ini) and restarting the service. The value applies globally to all InnoDB FULLTEXT indexes on the server.

[mysqld]
innodb_ft_min_token_size = 2

After the restart, existing FULLTEXT indexes must be rebuilt. WooCommerce does not handle this automatically. You can force a rebuild by toggling the FTS option off and back on, or running OPTIMIZE TABLE directly against the affected tables via WP-CLI or phpMyAdmin.

SET GLOBAL innodb_optimize_fulltext_only = ON;
OPTIMIZE TABLE wp_wc_order_addresses;
OPTIMIZE TABLE wp_woocommerce_order_items;
SET GLOBAL innodb_optimize_fulltext_only = OFF;

On a table with 500k+ rows, rebuilding the FULLTEXT index can take 30-90 seconds depending on hardware. Run it during a maintenance window or on a staging environment first.

Known Bugs and Tokenization Quirks

Phone number search is the most frequently reported issue. When FTS is active, searching by billing phone number fails entirely because the default parser treats sequences of digits differently than alphabetic tokens. This was confirmed as a bug in WooCommerce 9.3 and tracked on GitHub. Disabling FTS restores phone number search immediately, which points to tokenization rather than missing data.

Multi-word product name searches can also return unexpected results. Searching for “Casual T-Shirt Blue XL” with FTS enabled may return every order containing any of those individual words rather than orders containing that specific product. The boolean mode default does not wrap the input in quotes for phrase matching, so each word gets treated as an independent token. Developers who need exact phrase matching can hook into the search query and modify the AGAINST clause to use quoted phrases.

Sequential order number plugins present another compatibility challenge. Plugins that store custom order numbers in meta fields and override the admin search via JavaScript or direct meta queries will conflict with the FTS code path. The WooCommerce team has published hooks for proper integration: woocommerce_hpos_admin_search_filters registers a new dropdown option in the search interface, and woocommerce_hpos_generate_where_for_search_filter modifies the SQL WHERE clause for that filter. Older plugins that intercept the search through JS overrides need updating to use these filter hooks instead.

Registering Custom Search Filters with FTS Hooks

The HPOS query system provides two hooks for extending FTS search. The first, woocommerce_hpos_admin_search_filters, adds entries to the search dropdown visible on the Orders screen. The second, woocommerce_hpos_generate_where_for_search_filter, controls what SQL gets generated when that filter is active.

add_filter('woocommerce_hpos_admin_search_filters', function (array $filters): array {
    $filters['custom_tracking'] = __('Tracking Number', 'your-textdomain');
    return $filters;
});

add_filter(
    'woocommerce_hpos_generate_where_for_search_filter',
    function (string $where, string $search_term, string $filter): string {
        if ('custom_tracking' !== $filter) {
            return $where;
        }
        global $wpdb;
        $where = $wpdb->prepare(
            " AND o.id IN (
                SELECT order_id FROM {$wpdb->prefix}wc_orders_meta
                WHERE meta_key = '_tracking_number'
                AND meta_value = %s
            )",
            $search_term
        );
        return $where;
    },
    10,
    3
);

This pattern keeps the FTS index handling intact for default searches while routing custom filter selections to a standard SQL path. The meta query above does not use FULLTEXT at all – it runs a direct equality check on the orders meta table. Mixing FTS and non-FTS queries within the same filter is possible but requires careful handling of the WHERE concatenation to avoid syntax conflicts.

One critical detail: when the search dropdown is set to “All,” WooCommerce loops through a hardcoded list of core filters defined in OrdersTableSearchQuery.php. Custom filters only fire when explicitly selected by the user. There is currently no hook to inject a custom filter into the “All” pass, which means custom meta fields will not appear in broad searches.

Stopwords, n-gram Parsers, and Index Size Trade-offs

MySQL ships with a default stopword list that excludes common English words like “the,” “and,” “for” from the index. For order address data, this is mostly harmless. The word “the” rarely matters when searching for a customer. But product names can include stopwords that matter – “The North Face” loses its first word, and “Pro” gets ignored if it falls below the token length.

Disabling stopwords entirely is possible via a server variable but inflates the index and degrades relevance scoring.

SET GLOBAL innodb_ft_enable_stopword = OFF;

The n-gram parser offers an alternative tokenization strategy that splits text into fixed-length character sequences rather than whole words. WooCommerce currently uses the built-in language parser, not n-gram. Switching to n-gram would improve partial matching – a search for “shirt” would match “t-shirt” – but the index size grows substantially because every possible n-character substring gets indexed. For a store with 500k order addresses, the n-gram index could easily double or triple in size compared to the language parser index.

The WooCommerce team has indicated plans to evaluate the n-gram parser if the default parser proves insufficient for international stores. CJK (Chinese, Japanese, Korean) scripts particularly benefit from n-gram because word boundaries in those languages are not marked by spaces. If the store serves a multilingual customer base, monitor the WooCommerce GitHub discussions for updates on parser selection.

Monitoring FTS Query Performance in Production

Query Monitor or a MySQL slow query log will confirm whether the FTS index is actually being used. Look for MATCH…AGAINST in the captured SQL. If you see LIKE ‘%term%’ instead, FTS is either disabled or the index creation failed.

-- Check current FTS-related variables
SHOW VARIABLES LIKE 'innodb_ft%';

-- Inspect the actual index contents for debugging
SET GLOBAL innodb_ft_aux_table = 'your_db/wp_wc_order_addresses';
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE LIMIT 20;

The INNODB_FT_INDEX_TABLE view exposes the actual tokenized entries in the index, which is useful for debugging why a specific search term returns no results. If the expected token is missing, the cause is usually a token length below innodb_ft_min_token_size, a match against the stopword list, or a character encoding mismatch.

On high-traffic stores processing 100+ orders per day, the FULLTEXT index needs periodic optimization. InnoDB marks deleted or updated rows in the index but does not physically remove them until OPTIMIZE TABLE runs. Scheduling this operation via a system cron job weekly keeps the index compact and search performance consistent.

# Weekly FTS index maintenance via system cron
0 3 * * 0 mysql -u woo_user -p'password' -e "SET GLOBAL innodb_optimize_fulltext_only=ON; OPTIMIZE TABLE wp_wc_order_addresses; OPTIMIZE TABLE wp_woocommerce_order_items; SET GLOBAL innodb_optimize_fulltext_only=OFF;" your_database

Should You Enable It on a Live Store Right Now?

The feature remains labeled “experimental” through WooCommerce 10.6 (March 2026). That label reflects edge cases around tokenization and plugin compatibility rather than fundamental instability. Stores that rely heavily on admin order search – fulfillment teams processing 50+ searches per shift, for example – will see immediate time savings. The risk is low because disabling the toggle removes the FTS code path instantly without touching order data.

Test on staging first. Verify that search workflows return the expected results with FTS active, especially phone number lookups and any custom order number plugins. Check MySQL version compatibility (avoid 8.0.29-8.0.35) and confirm innodb_ft_min_token_size matches the search patterns relevant to the store. If everything checks out, the toggle is safe for production.

Често задавани въпроси