Search

A relational LIKE '%term%' cannot use a B-tree index and scans the table, and it has no concept of relevance. Real search needs an inverted index: a map from each term to the documents containing it, which is what makes multi-term queries fast.

Analysis happens before indexing and determines what you can find. Tokenisation splits text into terms; lowercasing makes matching case-insensitive; stemming reduces words to a root so "running" matches "run"; stop-word removal drops high-frequency noise. The same analysis must run on the query — mismatched analysers are the most common reason a search returns nothing for an obviously present term.

Relevance. BM25 is the standard scoring function: terms rare across the corpus but frequent in a document score highest, with a saturation curve so repeating a word twenty times does not rank a document twenty times higher. Boost fields — a title match should outrank a body match.

Keeping it fresh. The search index is a denormalised copy, so it needs an update path and it will drift. Options are dual writes (simple, silently drifts on partial failure), change data capture from the database log (robust, more infrastructure), or periodic full reindexing (simple, stale between runs).

Vector search matches on embedding similarity rather than exact terms, which finds semantically related results that share no words. Hybrid search combines both, because pure vector search is weak at exact identifiers like SKUs or error codes.

Measure relevance, or you are guessing. Search quality is not something you can eyeball from a handful of queries you invented yourself. Build a judgement set — real queries paired with the results that should have come back — and track precision and recall against it, so a tuning change that helps one query while quietly breaking nine becomes visible. Click-through and abandonment from real traffic supply the rest, and they are the only signal reflecting what people actually wanted.

Design for the queries you will really get. They are short, misspelt, and frequently not sentences: a part number, half a product name, an error string pasted from a screen. Edit-distance tolerance handles typos, synonym lists close the gap between what you call something and what a customer calls it, and the no-results path deserves real design — a search that silently returns nothing is where people leave.