We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
2 system design questions lean on this idea. Each walks through the full answer.
System Design Masterclass · Database Types Storage
Free preview: the first 2 parts of this 8-part lesson.
Already bought the course? Sign in to read the whole lesson.
this lesson is part of
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design courseread the rest of this lesson in
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design coursein Database Types Storage
This lesson is part of the full System Design Masterclass. Unlock it along with 769 other lessons for a one-time payment.
One-time payment by card or PayPal
Get Lifetime AccessOne-time payment: UPI, card or netbanking
Get Lifetime Access18 Foundation lessons are free, no payment needed. Try them now
It is easy and good
“It is really nice and good Please join the course”
You're building a recipe website with 2 million recipes. A user searches for "easy chicken pasta dinner." Your first instinct, the instinct, is:
SELECT * FROM recipes WHERE title LIKE '%easy chicken pasta dinner%';
This returns nothing. There's no recipe with that exact substring in the title. Even if you search each word separately with OR and LIKE, you get horrible results: every recipe mentioning "easy" OR "chicken" OR "pasta" OR "dinner", thousands of irrelevant hits with no ranking.
The fundamental problem: LIKE does string matching. It looks for a literal sequence of characters. It doesn't understand that "running" and "ran" are the same concept. It doesn't know that a recipe titled "Quick Chicken Penne for Weeknight Dinners" is exactly what the user wants.
Full-text search solves this by treating text as language, not just characters. It breaks text into meaningful tokens, normalizes words to their root forms, weights results by relevance, and returns ranked results, the most relevant first.
Every search bar you've used. Google, Amazon, Stack Overflow, your email, uses full-text search internally. It's one of the most important capabilities in modern applications, and one of the most poorly understood.
Full-text search works in two phases: indexing (preparing the data) and querying (searching it). Both pass text through an analysis pipeline that transforms raw text into searchable tokens.
The analysis pipeline:
1. Character filtering. Strip HTML tags, convert special characters, normalize Unicode. <b>Café</b> becomes Cafe.
2. Tokenization. Split text into individual words (tokens). "Quick Chicken Penne" becomes ["Quick", "Chicken", "Penne"].
3. Lowercasing. Normalize case. "Quick" becomes "quick".
4. Stop word removal. Remove common words that add no meaning. "the," "is," "a," "for" are removed. "Chicken for dinner" becomes ["chicken", "dinner"].
5. Stemming or Lemmatization. Reduce words to their root form.
6. Synonym expansion (optional). "NYC" maps to "New York City". "couch" maps to "sofa".
After this pipeline, the raw text "Quick Chicken Penne for Weeknight Dinners" becomes tokens like ["quick", "chicken", "penne", "weeknight", "dinner"]. The user's search "easy chicken pasta dinner" becomes ["easi", "chicken", "pasta", "dinner"]. Now the overlap between query and document is clear. "chicken" and "dinner" match.