Full-Text Index

-- Create table with full-text index
CREATE TABLE articles (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    title   VARCHAR(200),
    body    TEXT,
    FULLTEXT idx_ft_title_body (title, body)  -- multi-column full-text index
);

-- Add to existing table
ALTER TABLE articles ADD FULLTEXT INDEX idx_ft_body (body);

-- Natural Language mode (default) — ranked by relevance
SELECT id, title, MATCH(title, body) AGAINST ('kafka streaming') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST ('kafka streaming')  -- WHERE uses index
ORDER BY relevance DESC
LIMIT 10;

-- EXPLAIN should show: Extra = "Using where; Ft_hints: sorted"
EXPLAIN SELECT * FROM articles
WHERE MATCH(title, body) AGAINST ('kafka streaming');

-- Minimum token length (default 3 — cannot search 2-char words)
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';  -- default: 3
-- To allow 2-char words: set innodb_ft_min_token_size=2 in my.cnf + OPTIMIZE TABLE

-- Boolean mode — must contain "kafka", must not contain "zookeeper"
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('+kafka -zookeeper' IN BOOLEAN MODE);

-- Must contain both "kafka" AND "streams"
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('+kafka +streams' IN BOOLEAN MODE);

-- Exact phrase match
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('"event sourcing"' IN BOOLEAN MODE);

-- Prefix wildcard: matches "stream", "streaming", "streams"
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('stream*' IN BOOLEAN MODE);

-- Combined: must have "kafka", optional "streams" (higher rank if present)
SELECT id, title,
       MATCH(title, body) AGAINST ('+kafka streams*' IN BOOLEAN MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('+kafka streams*' IN BOOLEAN MODE)
ORDER BY score DESC;

-- Full-text limitations and workarounds

-- 1. Stop words: common words excluded from indexing
SHOW VARIABLES LIKE 'innodb_ft_enable_stopword';  -- default: ON
-- View current stop word list:
SELECT value FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;
-- Disable stop words for specific table (my.cnf):
-- innodb_ft_user_stopword_table = 'my_db/my_stopwords'

-- 2. Minimum token size: words < 3 chars not indexed
-- Check: SELECT * FROM articles WHERE MATCH(body) AGAINST ('AI')
-- → returns 0 rows even if "AI" is in body (too short)

-- 3. Rebuild index after changing min_token_size
OPTIMIZE TABLE articles;  -- rebuilds full-text index

-- 4. InnoDB auxiliary tables (used by full-text engine)
SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE LIMIT 5;

-- When to use Elasticsearch instead of MySQL full-text:
-- ✓ Need fuzzy matching ("kafkka" → "kafka")
-- ✓ Need stemming ("searching" → "search")
-- ✓ Need multilingual tokenisation (Chinese, Japanese)
-- ✓ Searching across > 50M rows
-- ✓ Need faceted search / aggregations
-- ✓ Near-real-time search on rapidly changing data