Full-text search vs database LIKE

SELECT * FROM products
WHERE name LIKE '%jaket%';

text "jaket" · fuzzy 1 · boost title

What LIKE does

LIKE is pattern matching. '%jaket%' asks for rows where the column contains that exact sequence of characters anywhere, which the database answers by reading every row and comparing. A pattern that starts with a wildcard cannot use an index, because a B-tree is sorted by the start of the value and there is nothing to descend to. Case is a per-database question, accents another, and jaket is simply not a substring of jacket, so the typo finds nothing. When a pattern does match, every match is equal: there is no way to say the row whose title starts with the word is a better result than the row whose description mentions it in passing.

What full-text search does

Full-text search works on words. Tokenization splits text into terms, lowercases them and strips punctuation. Stemming reduces jackets to jacket so plural and singular match. Stop words drop the and of. Terms go into an inverted index - term to the list of records containing it - so a search is a lookup, not a scan. Phrase and proximity queries find rain jacket as two adjacent terms rather than two terms anywhere. Per-field weights let a match in the title count for more than a match in the description. And every result gets a relevance score built from how rare the term is, how often it appears and where, so results come back best-first.

Two consequences follow. The first is that the work is proportional to the matching records, not to the table: a term lookup returns the list of records containing jacket, and nothing else is read. The second is that the index knows things about the text that a column never records - how common a word is across the catalog, how many times it appears in a record, which field it sat in - and those are exactly the ingredients of a good ranking. Pattern matching has none of them, because it never looked at the text as text.

Typo tolerance

Edit distance counts how many single-character changes turn one term into another. jaket is one insertion from jacket, so with a fuzziness of one it matches. The lead visual shows the path.

One edit is usually right. Two edits is usually noise: at two, jaket also reaches basket, jacked and racket, and a product search that returns baskets for jackets has traded one failure for another. The practical setting is fuzziness of one, applied to terms of a sensible length, with exact matches scored above fuzzy ones so the typo-free query still wins.

Typeahead and suggestions

Typeahead matches a prefix as the user types, on a field built for it, so jac offers jacket, jackets, denim and jacquard before the word is finished. Suggestions complete the search after it runs: the products response carries Denim jacket and Rain jacket beside the results, drawn from what actually matched. Both come from the index in the same request as the results, which is what makes them instant.

Spell correction from your own data

A generic dictionary knows jacket. It does not know your brand names, your product codes or the model numbers your customers type. Build the dictionary from the catalog itself - the terms that actually appear in your products - and Did you mean corrects towards things you sell, not towards a word list. A catalog is the only dictionary that gets product names right.

Boosting

Relevance is a starting point; boosting is how the business adjusts it. Title above description, so a jacket named Denim jacket outranks a shirt whose description mentions jackets. Recent above old, so new arrivals surface. In-stock above out-of-stock, so the first page is buyable. Each is a weight on a field or a field value, applied at query time, and together they are the difference between a search that is technically correct and one that sells.

What about the database's full-text index?

MySQL, Postgres and SQL Server each have one, and for a single table with simple keyword search they are fine: a term lookup instead of a scan, some ranking, no leading-wildcard problem. Their limits are the ones that matter for a search page. Facets are still separate GROUP BY queries. Relevance control is thin. Typeahead and spell correction from your own data are not built in. And the query still runs on the production database, competing with the transactions, which is the problem the search page usually started with.

Different jobs, not better and worse. LIKE for a quick exact fragment on a small table. The database's full-text index for one table and simple needs. A search index when users search, filter and count across a catalog, and when the database has enough to do already.