The store that slows as it grows
At two hundred products the shop page with a colour filter and a price slider loads in half a second. At five thousand products, with three filters applied, it takes six, and the filter counts ("Red (43)") take longer than the products. Nothing changed but the catalogue size. The queries that were fine at small scale are quadratic or worse, and the database is doing the work the design assumed it never would.
Where the time goes
Attribute filtering is a term query. Filtering by colour means "products with a term in taxonomy pa_color matching red". WordPress resolves that through wp_term_relationships, which is fine for one taxonomy and gets expensive as filters combine: each additional attribute is another join or subquery over the relationships table.
Price filtering is a meta query. Price lives in wp_postmeta as _price. Filtering by range means a join to postmeta on a meta_value column that is stored as text, cast to a number per row, with no useful index. On a large catalogue this is a table scan.
Filter counts are the same query, repeated. The "(43)" beside each filter option is a COUNT for every term in every filterable attribute, given the current filters. Twenty colours, fifteen sizes, ten brands: forty-five count queries per page load, each with the joins above.
Variations multiply everything. A variable product with thirty variations has thirty rows of price meta and thirty sets of attribute terms. A five-thousand-product store with variations can be a hundred-thousand-row problem.
Sorting adds a final pass. Sort by price or popularity means ordering by a meta value across the full result set before pagination.
Stock status filtering ("hide out of stock") is another meta join, on _stock_status.
Every one of these is reasonable alone. The filter sidebar runs them all, together, on every filtered page view, with no cache because the combination of filters is nearly unique per visitor.
Seeing it
Query Monitor on a filtered shop page. Sort queries by time. You will see wp_postmeta joins with CAST(meta_value AS DECIMAL) in the price filter, dozens of near-identical COUNT queries from the layered nav widgets, and the main product query running hundreds of milliseconds. The slow query log on the database confirms which are worst under real load.
Fixes, in order of return
1. Use the product lookup table
WooCommerce 3.6 added wp_wc_product_meta_lookup, a table with one row per product holding min_price, max_price, stock_status, rating, total_sales and onsale as properly typed, indexed columns. Core's own sorting and price filtering use it when available. Make sure it is populated (WooCommerce, Status, Tools, "Product lookup tables: regenerate") and that any custom or plugin price filter is using it rather than joining postmeta. Filters that still hit _price meta on a store with this table populated are doing it the slow way by choice.
2. Use attribute lookup for filter counts
WooCommerce 6.x added wp_wc_product_attributes_lookup, a table designed for exactly the filter-count problem, and a setting (WooCommerce, Settings, Products, Advanced, "Use the product attributes lookup table") to have the layered navigation blocks and widgets use it. Enable it and regenerate the table. Filter counts drop from dozens of expensive joins to indexed lookups.
3. Reduce what the filter panel asks
Every filterable attribute is a set of count queries. Show the four that customers use, not the twelve the catalogue has. Hide counts if they are not helping. Show only terms with products. Each removal is queries not run.
4. Cache what can be cached
Filter counts for the unfiltered state, and for common single-filter states, can be cached with a transient or object cache keyed on the filter combination, invalidated on product save. Several performance plugins and the block-based filters do some of this. A persistent object cache (Redis) also makes the term and meta caches WooCommerce already uses actually persist between requests.
5. Add the missing indexes
On stores where custom queries still hit postmeta: an index on wp_postmeta (meta_key, meta_value(20)) helps equality lookups on short values. It does not fix numeric range queries on text columns; the lookup table does. Check wp_term_relationships and wp_term_taxonomy have their default indexes; some migrations lose them.
6. Trim variations
Stores that create a variation for every combination of five attributes have thousands of variations nobody buys. Fewer variations is fewer rows in every query above. Consider whether some attributes are informational (shown on the product) rather than purchasable (a variation).
7. Move filtering to a search index
Past a certain size, roughly ten thousand products or heavy variation counts, the database is the wrong tool for faceted search regardless of indexes. A search index (Elasticsearch via ElasticPress, Algolia, Typesense, Meilisearch) holds a flattened document per product with all attributes and prices as typed fields, and returns filtered, sorted, paginated results with facet counts in a single request measured in milliseconds. WooCommerce becomes the source of truth; the index serves the shop page. This is a project, and for large catalogues it is the answer.
Measuring the result
Before and after, on a filtered category page with three filters applied, on staging with production data: total page generation time and query count from Query Monitor, and TTFB from the browser. Stores that arrive at four to six seconds typically come under one with steps 1 to 4 alone. Step 7 is for stores that need to stay under one second as they grow.
Where this sits
Slow filtering is the most common performance complaint on catalogue-heavy stores, and the two lookup tables are the fix most of them are missing, because both are opt-in and neither is advertised. Enabling them and regenerating is a standard early step in WooCommerce performance work; the search index conversation follows when the catalogue size says it should.