Faceted search that stays fast past fifty filters

Faceted search - filter by category, then brand, then price, then spec, each option showing a live count - is one of those features that looks trivial in a demo with twenty products and turns into the slowest page on the site once a real catalog is loaded.

We built this for a niche-gadget storefront spanning six product categories - rugged phones, RC cars, projectors, power stations, gaming consoles - each with its own brand and spec filters. Enthusiast buyers filter aggressively before they ever look at a product, so the filter UI had to stay instant, not just eventually-correct.

1. The naive version doesn't survive real traffic

The obvious implementation runs a `COUNT(*)` query per facet option, for every combination of filters already applied, on every page load. With a handful of products that's invisible. With a real catalog and a dozen active facets, it's dozens of aggregate queries stacked on a single request - and it gets slower exactly when the catalog you're proud of gets bigger.

2. Precompute counts, don't calculate them live

We moved facet counting out of the request path entirely. A background job maintains a denormalised facet-count table, keyed by category and the filters applied within it, recalculated on a schedule (or triggered on stock/price changes) rather than on every visitor's click. The storefront then reads pre-aggregated numbers instead of computing them fresh - a lookup instead of a scan.

This trades perfect real-time accuracy (a count might lag a live stock change by a few minutes) for consistent speed at any traffic level - a trade every high-traffic catalog makes, whether it's visible to the shopper or not.

3. Index for the filters you actually combine

Generic single-column indexes help less than expected once shoppers stack three or four facets together. We built composite indexes around the filter combinations that actually happen - category plus brand, category plus price band - based on real query logs, not guesswork. A handful of well-chosen composite indexes covered the overwhelming majority of filter combinations shoppers used.

4. Keep the URL and the state in sync

• Every filter combination gets a real, bookmarkable URL - shoppers share filtered views, and search engines can index a well-chosen subset of them.
• Selecting a filter updates counts for the remaining facets, not just the product grid - a filter that would return zero results should say so before it's clicked.
• Debounce filter changes client-side so dragging through a price slider doesn't fire a request per pixel.
• Cache the fully-rendered result for popular filter combinations at the CDN layer, not just the database query.

5. Canonicalise near-duplicate filter states

The same product set is reachable through several different filter orderings and query-parameter arrangements. Without a canonical URL per distinct result set, you end up with dozens of near-identical indexable pages competing with each other - a self-inflicted duplicate-content problem. We normalise filter parameters into a consistent order before generating the canonical tag, so `?brand=x&cat=y` and `?cat=y&brand=x` always point at one URL.

A facet filter should feel like flipping a light switch. The moment it feels like loading a new page, shoppers stop exploring and start leaving.

How do you keep faceted search fast with a large product catalog?

Stop calculating facet counts live - precompute them in a background job into a denormalised count table, and read from it on every request instead of running aggregate queries per filter. Build composite indexes around the filter combinations shoppers actually stack together, and cache popular filtered pages at the CDN layer.