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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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

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.

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.

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.

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

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.

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

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.

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 .

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 ).

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary