Full-Text Search & Fuzzy Matching

-- The "obvious" way to search text: LIKE with wildcards
SELECT id, title
FROM articles
WHERE body LIKE '%database%';

-- This works on tiny tables, but it has three deal-breakers:
--   1. It can't use a normal index, so it reads EVERY row
--      and scans EVERY character — a full table scan.
--   2. It can't rank results. A title hit and a hit buried
--      in paragraph 90 come back equally "matched".
--   3. It's literal: 'database' won't match 'databases',
--      'Database', or a search fo
...

-- to_tsvector turns text into a tsvector: a sorted list of
-- normalised "lexemes" (root words) with their positions.
SELECT to_tsvector('english',
    'PostgreSQL is a powerful open-source database system');

-- Result (one tsvector value):
--   'databas':6 'open-sourc':4 'postgresql':1 'power':3
--   'sourc':5 'system':7
-- Notice what happened:
--   • 'powerful' became 'power'      (stemming)
--   • 'database' became 'databas'    (stemming)
--   • 'is' and 'a' vanished          (stop words r
...

-- to_tsquery builds the SEARCH side. It is also stemmed,
-- so your search word matches every form of the word.
SELECT to_tsquery('english', 'powerful & databases');
-- Result:  'power' & 'databas'

-- The @@ operator asks "does this document match this query?"
SELECT to_tsvector('english', 'PostgreSQL is a powerful database')
       @@ to_tsquery('english', 'powerful & databases');
-- Result:  true
--   't' even though the text says "database" (singular) and
--   the query said "databases" (pl
...

-- tsquery has a small operator language:
--   &  AND   (both terms must appear)
--   |  OR    (either term)
--   !  NOT   (exclude a term)
--   <-> FOLLOWED BY  (phrase: the words are adjacent)

-- Articles about databases but NOT about MySQL:
SELECT id, title FROM articles
WHERE to_tsvector('english', body)
      @@ to_tsquery('english', 'database & !mysql');

-- The exact phrase "full text search":
SELECT id, title FROM articles
WHERE to_tsvector('english', body)
      @@ to_tsquery('english'
...

-- @@ only answers yes/no. ts_rank gives a relevance SCORE
-- (higher = more relevant) so you can ORDER results like Google.
SELECT id, title,
       ts_rank(to_tsvector('english', title || ' ' || body), query) AS rank
FROM articles,
     plainto_tsquery('english', 'database performance') AS query   -- run query once
WHERE to_tsvector('english', title || ' ' || body) @@ query
ORDER BY rank DESC;

-- ts_rank scores on term frequency and position.
-- ts_rank_cd ("cover density") also rewards terms
...

-- Speed: by default Postgres recomputes to_tsvector() for every
-- row on every query. A GIN index pre-stores the lexemes so the
-- search jumps straight to matching rows — instant on millions.

-- Best practice (Postgres 12+): a STORED generated column,
-- so the tsvector is always in sync with the text automatically.
ALTER TABLE articles
    ADD COLUMN search tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector
...

-- MySQL does full-text search with a FULLTEXT index and
-- the MATCH(...) AGAINST(...) pair instead of tsvector/tsquery.

-- 1) Add a FULLTEXT index over the columns you'll search:
CREATE FULLTEXT INDEX idx_articles_ft ON articles (title, body);

-- 2) Natural-language mode (the default): MySQL ranks by relevance.
--    Putting MATCH in the SELECT returns that relevance score.
SELECT id, title,
       MATCH(title, body) AGAINST('database performance') AS score
FROM articles
WHERE MATCH(title, b
...

-- Full-text search needs the RIGHT word (after stemming).
-- For typos and misspellings you need FUZZY matching.

-- (a) Trigram similarity — pg_trgm. It compares the 3-letter
--     chunks two strings share and scores them from 0 to 1.
CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT similarity('database', 'databse');   -- ~0.55  (typo still close)
SELECT title FROM articles
WHERE similarity(title, 'databse') > 0.3     -- catches the misspelling
ORDER BY similarity(title, 'databse') DESC;
-- In
...