START WITH A PROBLEM

Slow product or catalog search

A search box backed by LIKE '%term%' cannot forgive a typo, rank a result or count what is left after a filter.

Business Engineering PROBLEM 2 min read

Today

-- the search box, today
SELECT * FROM products
WHERE name LIKE '%jaket%';

0 rows · every row scanned · no ranking, no counts

With a search index

POST /search/query/products
text "jaket" · fuzzy 1 · facets brand, size

200 OKone response

531

Records found

42.60

Avg price

Suggestions

Denim jacket
Rain jacket

Brands

Polo302
Levis144
Nike85

Same typo. One request. Results, suggestions and counts.

The symptom

A customer types jaket and gets nothing. Another types jacket and gets 531 results in no useful order, with the item they wanted on page six. The filter sidebar shows brands and sizes but no counts, or counts that take a second query each, so the page assembles itself in stages. And the search page is the slowest page on the site, which is a problem, because it is also the page most visitors use first.

Internal catalogs have the same shape: a support agent looking up a product by a half-remembered name, a buyer filtering a few hundred thousand SKUs by supplier and price band.

Why it happens

The search box is backed by LIKE '%jaket%'. A pattern with a leading wildcard cannot use a database index, so every search reads every row of the products table. It matches characters, not words: jaket is not a substring of jacket, so the typo returns nothing, and jackets would miss jacket the same way.

Relevance is not a database concept. A row either matches the pattern or it does not, so there is no way to put the best match first. And counting what is left after a filter - how many per brand, per size, per price band - is three more queries, each scanning the same filtered set again. Every element the user expects from a search page is a separate scan of the same table.

What changes with an indexed layer

A search index stores the catalog by word, not by row. Full-text search with typo tolerance matches jaket to jacket by edit distance and ranks the best matches first. Typeahead offers Denim jacket and Rain jacket as the user types, and the spelling dictionary is your own catalog, so product names that no general dictionary knows are still corrected properly.

Filters come with counts, computed on the same filtered set as the results, in the same request. The products request used across this site shows the shape: text jaket, category Clothing, facets on brand and size, a price range and an average. One response: 531 records, Polo 302 · Levis 144 · Nike 85, Small 412 · Medium 88 · Large 31, the price bands, the average price of 42.60. The whole page, from one call, and the production database is not involved.