Full Text Search

By the end of this lesson you'll be able to build real, scalable search: index your text, find documents by meaning (not just exact characters), rank the hits by relevance, and tolerate typos. You'll see exactly why LIKE '%word%' falls apart — and what professionals reach for instead.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

Our Sample Table: articles

Every query in this lesson searches this little articles table. The body column is shown shortened, but full-text search reads the whole thing.

1. Why Not Just LIKE '%word%' ?

You already know how to filter text with LIKE . So why does every serious app use something else for search? Because a leading-wildcard LIKE '%word%' has three problems that get worse as your data grows.

LIKE '%word%' is reading every book in the library cover to cover to find a word. Full-text search is the index at the back of each book that a librarian built ahead of time — ask for "database optimization" and she pulls the right books instantly, sorted by how relevant they are.

The fix is to pre-process text into a searchable form once, index it, and then search the index. That's what full-text search does.

2. PostgreSQL: tsvector , tsquery & @@

PostgreSQL splits search into two data types. A tsvector is the document side: your text broken into normalised root words (called lexemes ). A tsquery is the search side: the words you're looking for. The @@ operator returns true when the document matches the query.

Your Turn: complete the match

Fill in the blanks so the query finds every article whose title or body mentions index . Hint: use the friendly query builder for a single plain word. The expected result is in the comments so you can check yourself.

3. Boolean & Phrase Search

Beyond single words, to_tsquery understands a small operator language so users can be precise: combine terms, exclude terms, or demand an exact phrase.

4. Ranking Results with ts_rank

The @@ operator only answers yes/no — but real search shows the best result first. ts_rank(document, query) returns a relevance score (higher is better) based on how often the terms appear and where. Sort by it with ORDER BY rank DESC .

Listing the query once in the FROM clause ( plainto_tsquery(…) AS query ) lets you reuse it in both the SELECT and the WHERE without building it twice. Prefer ts_rank_cd when how close the words sit to each other matters.

Your Turn: add relevance ranking

The WHERE already finds the matches. Add the scoring function and the sort direction so the most relevant article comes back first.

5. Make It Fast: GIN Index & Weights

Everything so far recomputes to_tsvector() for every row on every query — fine for demos, far too slow for millions of rows. Store the vector in a column and put a GIN index on it (Generalised INverted index — the same idea as a book's back-of-book index). With setweight() you also tag where each word came from so title hits outrank body hits.

6. MySQL: MATCH() AGAINST()

MySQL takes a different route. You add a FULLTEXT index over the text columns, then search with MATCH(columns) AGAINST('terms') . Natural-language mode (the default) ranks by relevance automatically; boolean mode unlocks + / - / "phrase" operators much like Postgres' tsquery .

7. Fuzzy Matching for Typos

Full-text search needs the right word (after stemming). But users misspell things — "databse", "Samsnug". Fuzzy matching measures how similar two strings are so a near-miss still matches. Two common tools, both high-level here: trigram similarity ( pg_trgm ) and Levenshtein distance ( fuzzystrmatch ).

Common Errors (and the fix)

• No index, so search crawls: querying to_tsvector(body) @@ … with no GIN index (Postgres) or no FULLTEXT index (MySQL) silently does a full table scan. It "works" on 100 rows and times out on a million — add the index before you ship.
• Using LIKE for real search: LIKE '%term%' can't use a normal index, can't stem, and can't rank. Reach for it only for tiny tables or exact substring checks, never for a search box.
• Ignoring stop-words: searching for 'the' & 'of' returns nothing — those words were stripped from the vector. Search on the meaningful words, and remember a search for databases matches database thanks to stemming.
• Forgetting to rank: filtering with @@ but no ts_rank + ORDER BY returns matches in arbitrary order. Always score and sort, or your "search" lists the worst hit first.
• Wrong tsquery for user input: feeding a raw phrase to to_tsquery ( to_tsquery('english', 'database performance') ) errors on the space — use plainto_tsquery for plain user text.

📘 Quick Reference

Syntax

Purpose

Frequently Asked Questions

For exact substring checks on small tables, or a prefix match like LIKE 'abc%' (no leading wildcard), which can use a normal index. For a real search box over lots of text, use full-text search.

Q: What's the difference between to_tsquery and plainto_tsquery ?

to_tsquery expects operator syntax ( 'database & !mysql' ) and errors on a bare space. plainto_tsquery takes free text from a user and ANDs the words for you — safer for search-box input.

Q: Do I need a GIN index to use full-text search?

No — it works without one, but every query rescans the table. Once your data grows, a GIN index (Postgres) or FULLTEXT index (MySQL) is what makes search fast.

Q: Full-text search vs. fuzzy matching — which do I want?

Both. Full-text finds documents about a topic (with stemming and ranking); fuzzy matching ( pg_trgm , Levenshtein) catches typos . Production search usually layers full-text first with a fuzzy fallback.

Mini-Challenge: A Ranked Search, End to End

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a PostgreSQL playground to confirm.

🎉 Lesson Complete

• ✅ LIKE '%word%' can't scale, stem, or rank — full-text search can
• ✅ to_tsvector + to_tsquery matched with @@ (with stemming & stop-words)
• ✅ ts_rank scores relevance so you can ORDER BY rank DESC
• ✅ A GIN index on a weighted tsvector column makes it fast
• ✅ MySQL does the same with MATCH() AGAINST() and a FULLTEXT index
• ✅ Fuzzy tools ( pg_trgm , Levenshtein) tolerate typos
• ✅ Next: storing and querying JSON & semi-structured data

Practice quiz

Continue this course

• Previous: Temporal Tables
• Next: JSON Data

Related lessons

• SELECT
• WHERE
• indexes