Full-Text Index #

-- A search query that looks reasonable
SELECT id, title, content FROM articles WHERE content LIKE '%database%';

-- Its EXPLAIN:
-- +------+------+------+----------+-------------+
-- | type | key  | rows | filtered | Extra       |
-- +------+------+------+----------+-------------+
-- | ALL  | NULL | 850000 | 11.11 | Using where |
-- +------+------+------+----------+-------------+
-- type = ALL → full table scan
-- rows = 850,000 → every row must be read

-- Why can't an index help?
-- A B-Tree index is sorted by the string's starting values.
-- An index can help WHERE content LIKE 'database%' (fixed prefix)
-- But CANNOT help WHERE content LIKE '%database%'
-- because the database doesn't know where in the string that word appears.

-- On a new table
CREATE TABLE articles (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title      VARCHAR(500) NOT NULL,
    content    MEDIUMTEXT NOT NULL,
    status     VARCHAR(20) NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_articles_status (status),
    FULLTEXT INDEX ft_articles_search (title, content)  -- the FTI covers two columns
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Adding an FTI to an existing table
ALTER TABLE articles
ADD FULLTEXT INDEX ft_articles_search (title, content);

-- An FTI can cover several columns — the MATCH query must name all columns
-- in the index, not just some of them

-- NATURAL LANGUAGE MODE (default): searches words, sorts by relevance
SELECT id, title,
       MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE)
  AND status = 'published'
ORDER BY score DESC
LIMIT 20;
-- Fits: general search engines, "find the most relevant articles"
-- Weakness: overly common words (appearing in > 50% of rows) are automatically ignored

-- BOOLEAN MODE: explicit control with operators
SELECT id, title
FROM articles
WHERE MATCH(title, content) AGAINST(
    '+database +optimization -beginner'
    IN BOOLEAN MODE
)
  AND status = 'published'
LIMIT 20;
-- + means the word MUST be present
-- - means the word must NOT be present
-- No prefix: optional (increases relevance if present)
-- "phrase" (in quotes): must appear as a sequential phrase
-- Fits: precise filters, searches with exclusions

-- A complete BOOLEAN MODE operator example:
SELECT id, title FROM articles
WHERE MATCH(title, content) AGAINST(
    '+postgresql +"query optimization" -beginner tutorial*'
    IN BOOLEAN MODE
);
-- postgresql   : must be present
-- "query optimization" : this phrase must appear in sequence
-- -beginner    : don't show beginner articles
-- tutorial*    : wildcard — matches "tutorial", "tutorials", etc.

-- LIMITATION 1: Minimum word length (default: 3 characters for InnoDB)
-- Short words aren't indexed!
SELECT @@innodb_ft_min_token_size;  -- default: 3
-- "go", "db", "id" won't be indexed or searchable

-- Change it if you need short-word support:
SET GLOBAL innodb_ft_min_token_size = 2;
-- After changing: REBUILD all fulltext indexes!
-- ALTER TABLE articles DROP INDEX ft_articles_search;
-- ALTER TABLE articles ADD FULLTEXT INDEX ft_articles_search (title, content);

-- LIMITATION 2: Stopwords — words always ignored
-- MySQL has a built-in stopword list: "the", "a", "is", "in", "at", etc.
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
-- "about", "are", "as", "at", "be", "by", "com", "for", "from", ...

-- Consequence: searching "how to use database" only searches "database"
-- "how", "to", "use" are stopwords!

-- Disable stopwords for technical contexts:
SET GLOBAL innodb_ft_enable_stopword = 0;
-- Or use a custom stopword list

-- LIMITATION 3: Words appearing in > 50% of rows are ignored in NATURAL LANGUAGE
-- If 60% of articles contain the word "mysql", that word is useless for relevance
-- Use BOOLEAN MODE to override this behavior

-- LIMITATION 4: A minimum of 3 rows of data
-- An FTI won't return results if the table has < 3 rows
-- This often causes confusion in test environments with minimal data

-- Approach 1: Full-Text Index using GIN (Generalized Inverted Index)
-- GIN fits documents — fast queries but slower updates
CREATE INDEX idx_articles_fts
ON articles USING GIN(to_tsvector('english', title || ' ' || content));

-- Approach 2: GIST index — faster updates but slightly slower queries
CREATE INDEX idx_articles_fts_gist
ON articles USING GIST(to_tsvector('english', title || ' ' || content));

-- Query with to_tsvector + to_tsquery
SELECT id, title,
       ts_rank(
           to_tsvector('english', title || ' ' || content),
           to_tsquery('english', 'database & optimization')
       ) AS rank
FROM articles
WHERE to_tsvector('english', title || ' ' || content)
      @@ to_tsquery('english', 'database & optimization')
  AND status = 'published'
ORDER BY rank DESC
LIMIT 20;

-- Add a tsvector column updated automatically
ALTER TABLE articles
ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(content, '')), 'B')
    ) STORED;
-- setweight 'A' for title (higher weight)
-- setweight 'B' for content (lower weight)

-- Create a GIN index on the generated column
CREATE INDEX idx_articles_search_vector
ON articles USING GIN(search_vector);

-- The query is now much cleaner and faster
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & optimization') query
WHERE search_vector @@ query
  AND status = 'published'
ORDER BY rank DESC
LIMIT 20;
-- search_vector is already stored → no need to compute to_tsvector per query

-- to_tsquery: words separated by logical operators
to_tsquery('english', 'database & optimization')  -- AND: both words must be present
to_tsquery('english', 'database | mysql')          -- OR: either or both
to_tsquery('english', 'database & !beginner')      -- NOT: database but not beginner
to_tsquery('english', 'optim:*')                   -- prefix: optimization, optimistic, etc.

-- plainto_tsquery: free input, no explicit operators needed
plainto_tsquery('english', 'database optimization tutorial')
-- automatically interpreted as AND

-- phraseto_tsquery: sequential phrase search
phraseto_tsquery('english', 'query optimization')
-- searches for "query" directly followed by "optimization"

-- websearch_to_tsquery: Google-Search-like syntax
websearch_to_tsquery('english', 'database optimization -beginner "index scan"')
-- More user-friendly for search box input

-- ANTI-PATTERN: an FTI without additional filters
SELECT id, title, content
FROM articles
WHERE MATCH(title, content) AGAINST('database' IN NATURAL LANGUAGE MODE);
-- Might return 100,000 rows for a common word
-- Scoring and ranking 100,000 rows is very expensive

-- CORRECT: an FTI combined with B-Tree filters
SELECT id, title,
       MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE status = 'published'          -- B-Tree filter: index on status
  AND category_id IN (1, 5, 12)     -- B-Tree filter: index on category_id
  AND created_at >= '2025-01-01'    -- B-Tree filter: index on created_at
  AND MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;
-- The database first filters with B-Trees (maybe leaving 5,000 relevant articles)
-- Then the FTI runs on those 5,000 articles, not the entire table

-- In MySQL: check whether the FTI or B-Tree is used first
EXPLAIN SELECT id, title
FROM articles
WHERE status = 'published'
  AND MATCH(title, content) AGAINST('database' IN BOOLEAN MODE);

-- Expected output:
-- +------+-------+--------------------------+-------+------+---------------------------+
-- | type | key   | key_len                  | rows  | Extra                       |
-- +------+-------+--------------------------+-------+------+---------------------------+
-- | fulltext | ft_articles_search | 0 | 1 | Using where; Ft_hints: no_ranking |
-- +------+-------+--------------------------+-------+------+---------------------------+

-- If MySQL chooses the FTI alone and ignores the status index:
-- Use FORCE INDEX to force the order
SELECT id, title
FROM articles USE INDEX (idx_articles_status, ft_articles_search)
WHERE status = 'published'
  AND MATCH(title, content) AGAINST('database' IN BOOLEAN MODE);

-- MySQL: show the relevance score for debugging
SELECT
    id,
    title,
    MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE) AS relevance_score
FROM articles
WHERE MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE)
  AND status = 'published'
ORDER BY relevance_score DESC
LIMIT 10;

-- A high score = more relevant
-- If all scores are very low (< 0.1): the word may be too common or absent from the index

-- PostgreSQL: show a headline (snippet with highlighting)
SELECT
    id,
    title,
    ts_rank(search_vector, query) AS rank,
    ts_headline('english', content, query,
        'MaxWords=50, MinWords=20, ShortWord=3, HighlightAll=FALSE'
    ) AS snippet
FROM articles, to_tsquery('english', 'database & optimization') query
WHERE search_vector @@ query
  AND status = 'published'
ORDER BY rank DESC
LIMIT 10;
-- ts_headline produces a content snippet with highlighted keywords
-- Useful for showing search result previews to users

-- ✗ Anti-pattern 1: LIKE '%keyword%' for searches on large tables
SELECT * FROM articles WHERE content LIKE '%database%';
-- → Full table scan, indexes useless
-- ✓ Solution: use a FULLTEXT INDEX with MATCH ... AGAINST

-- ✗ Anti-pattern 2: FTIs without B-Tree filters
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database');
-- → Can return hundreds of thousands of rows for common words
-- ✓ Solution: always add WHERE status = ? or other filters before the FTI

-- ✗ Anti-pattern 3: ORDER BY MATCH ... AGAINST without LIMIT
SELECT *, MATCH(title, content) AGAINST('database') AS score
FROM articles
WHERE MATCH(title, content) AGAINST('database')
ORDER BY score DESC;
-- → Compute scores for every matching row, sort them all — very expensive
-- ✓ Solution: always add LIMIT 20 or pagination

-- ✗ Anti-pattern 4: FULLTEXT INDEXes on frequently updated columns
-- E.g. indexes on notes, last_comment, activity_log columns
-- → Every UPDATE to those columns rebuilds part of the inverted index
-- ✓ Solution: FTIs only for rarely changed columns (main title, content)

-- ✗ Anti-pattern 5: searching short words without changing the min token size
-- MATCH(title) AGAINST('go' IN BOOLEAN MODE) → returns no results
-- because "go" (2 characters) is below the default minimum token size (3)
-- ✓ Solution: change innodb_ft_min_token_size = 2 and rebuild the index

-- ✗ Anti-pattern 6: not handling stopwords in NATURAL LANGUAGE MODE
-- A user searches "how to use index" → only "index" is searched
-- "how", "to", "use" are stopwords → users wonder why results are so few
-- ✓ Solution: use BOOLEAN MODE for queries needing precision,
--   or disable the stopword list, or use an external search engine