Fast filters in large catalogues: without killing the database or the index
Filters are the most loaded part of a catalogue, solving two conflicting problems at once: answering instantly and not spawning millions of junk URLs.
Why filters get slow
The classic cause is attributes stored vertically in a property-value table, where each selected filter adds another JOIN. At five conditions the query takes seconds; as the catalogue grows, tens of seconds.
The second is counting total results and per-option counters. A full COUNT over a large result set is often more expensive than fetching the first page of products.
The third is sorting by a computed field such as final discounted price. If it cannot be indexed, the database sorts the entire result set on every request.
What to do with the data schema
Denormalise the most frequently filtered attributes into indexed product columns. An EAV schema is convenient for editors, but the user pays for that flexibility in latency.
Build composite indexes around real filter combinations rather than one index per column. Column order matters: equality predicates first, then range, then sort.
Where there are dozens of filters and hundreds of combinations, move search into a search engine with an inverted index rather than tuning SQL further. Facets and counters then come from one query while the database stays the source of truth.
Caching and counters
Cache filter results under a normalised key: sorted parameter set, page, sort order. Different parameter ordering in the URL must not produce different cache keys.
Per-option product counts are useful but expensive. The compromise is computing them with a small refresh lag rather than in real time, and hiding options that yield nothing.
Tie cache invalidation to price and stock updates from the accounting system. With syncs every 10–15 minutes, flush selectively by affected category rather than wholesale.
URLs, indexation and duplicates
For one or two popular filters, create dedicated clean-URL pages like /catalogue/pumps/submersible/, open to indexing with their own title, description and copy. That is the main source of long-tail traffic.
Keep every other combination on GET parameters and close them off: canonical to the base category plus noindex, follow for multi-facet combinations. Without this the catalogue emits endless URLs and burns crawl budget.
Sorting and pagination must not create indexable duplicates: sort views canonicalise to the base page, while paginated pages keep self-referential canonicals and unique titles carrying the page number.
Interface and perceived speed
On mobile, open filters as a bottom sheet with an apply button and a result counter. Applying every checkbox instantly feels like a freeze on 3G: three ticks means three round trips.
Show active filters as chips above the list with individual removal and a clear-all button. A user who cannot tell why only four products remain simply leaves.
Filter state must live in the URL so the page can be shared and survives the back button. For a Telegram-heavy local audience that matters especially — selections get pasted into chats.
Ranges, priorities and measurement
Give the UZS price range numeric inputs, not just a slider: hitting a value with a slider on a phone is hard and the amounts are long. Apply the value a few hundred milliseconds after typing stops.
Order filters by usage frequency rather than alphabetically. The first two or three should be the ones people actually use; collapse the rest.
Log applied filter combinations and empty result sets. A frequent combination with no products is either an assortment gap or a candidate for its own landing page.