FULLTEXT Search

-- 1) Create a FULLTEXT index
CREATE TABLE posts (
    id      BIGINT AUTO_INCREMENT PRIMARY KEY,
    title   VARCHAR(255) NOT NULL,
    body    TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT (title, body)         -- in-line FULLTEXT covering both columns
) ENGINE=InnoDB;

-- Or after creation:
ALTER TABLE posts ADD FULLTEXT (title, body);
DROP INDEX idx_name ON posts;
ALTER TABLE posts DROP INDEX title;

-- 2) Natural-language mode (default — phrase-based, ranked)
SELECT id, title, MATCH(title, body) AGAINST('docker container') AS score
FROM   posts
WHERE  MATCH(title, body) AGAINST('docker container')
ORDER BY score DESC
LIMIT 20;

-- 3) Boolean mode — supports operators
SELECT id, title
FROM   posts
WHERE  MATCH(title, body) AGAINST('+docker -mongo +(container|kubernetes)' IN BOOLEAN MODE);

-- Boolean operators:
--   +word     — must contain
--   -word     — must not contain
--   (a | b)   — OR
--   *         — wildcard suffix (docker* matches dockerfile, dockers)
--   "phrase"  — exact phrase
--   word*     — prefix match (suffix only)
--   ~word     — lower the row's relevance if present
--   >word     — increase relevance
--   <word     — decrease relevance

SELECT * FROM posts WHERE MATCH(title, body) AGAINST('+"docker compose" +kubernetes -nginx' IN BOOLEAN MODE);

-- 4) Query expansion (broaden results)
SELECT * FROM posts WHERE MATCH(title, body) AGAINST('docker' WITH QUERY EXPANSION);
-- Runs query twice; second iteration includes top-ranked terms from first.

-- 5) Inspect default settings
SHOW VARIABLES LIKE 'ft_%';
SHOW VARIABLES LIKE 'innodb_ft_%';

-- ft_min_word_len             — minimum token length to index (default: 4 MyISAM, 3 InnoDB)
-- innodb_ft_min_token_size   — InnoDB equivalent
-- innodb_ft_max_token_size
-- innodb_ft_enable_stopword
-- innodb_ft_server_stopword_table

-- 6) Custom stopwords
CREATE TABLE my_stopwords (value VARCHAR(30));
INSERT INTO my_stopwords VALUES ('foo'), ('bar'), ('our_brand');

SET GLOBAL innodb_ft_server_stopword_table = 'mydb/my_stopwords';
-- Then drop + recreate FULLTEXT index for the new stopwords to apply
ALTER TABLE posts DROP INDEX title;
ALTER TABLE posts ADD FULLTEXT (title, body);

-- 7) ngram parser — for CJK languages
ALTER TABLE posts ADD FULLTEXT INDEX idx_cjk (title, body) WITH PARSER ngram;

SELECT * FROM posts WHERE MATCH(title, body) AGAINST('容器' IN BOOLEAN MODE);
-- ngram tokens are 2-char by default; tweak with ngram_token_size variable

-- 8) Inspect tokens (debugging)
SET GLOBAL innodb_ft_aux_table = 'mydb/posts';
SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE WHERE word LIKE 'docker%';

-- 9) Combine with regular filters
SELECT id, title, MATCH(title, body) AGAINST('docker') AS score
FROM   posts
WHERE  MATCH(title, body) AGAINST('docker')
   AND created_at >= NOW() - INTERVAL 90 DAY
   AND status = 'published'
ORDER BY score DESC, created_at DESC
LIMIT 20;

-- 10) Ranking + recency boost (manual)
SELECT id, title,
       MATCH(title, body) AGAINST('docker') * 1.0
       + (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(created_at)) / -86400 * 0.001 AS combined_score
FROM   posts
WHERE  MATCH(title, body) AGAINST('docker')
ORDER BY combined_score DESC
LIMIT 20;

-- 11) Multi-column FULLTEXT with weighted columns
-- Title should rank higher than body. MySQL doesn't let you weight columns in MATCH directly.
-- Workaround: search them separately and combine:
SELECT id, title,
       MATCH(title) AGAINST('docker') * 5.0
     + MATCH(body)  AGAINST('docker') * 1.0  AS combined
FROM   posts
WHERE  MATCH(title, body) AGAINST('docker')
ORDER BY combined DESC;

-- (Need separate FULLTEXT indexes on (title) and (body) to use them separately.)
-- Or store title twice (in title and at the start of body) — yikes.

-- 12) Partial / typo matching — not natively supported
-- For 'dokcer' to match 'docker', use LIKE or external search engine.
-- LIKE with leading wildcard ('%docker%') can't use indexes — slow.

-- For autocomplete: use a separate column with normalized text + a regular index,
-- or use a column-store / search engine (Meilisearch, Typesense).

-- 13) Performance
-- - FULLTEXT indexes are bigger than B-tree indexes (more disk + RAM)
-- - Updates rebuild parts of the inverted index — expensive on hot tables
-- - Use a SEPARATE FULLTEXT table for posts (mirror text into a fts table) if writes are heavy
--   Sync via triggers / app code.

-- 14) Limitations
--   • Numeric / date filtering must be done in WHERE (FULLTEXT only indexes text)
--   • One row at a time — MATCH ... AGAINST doesn't return per-token data
--   • Stopwords + min word length cut out small / common words — 'the' won't match
--   • Boolean mode AGAINST doesn't auto-stem (docker won't match dockers in BOOLEAN mode!)
--     vs natural-language mode does basic stemming
--   • No phrase ranking weighting per field

-- 15) When to look beyond FULLTEXT
--   • Typo tolerance (Meilisearch, Typesense, Algolia)
--   • Faceted search + filters (Elasticsearch, OpenSearch)
--   • Synonyms / weighted fields / multilingual stemming
--   • Real-time autocomplete with sub-50ms latency
--   • >10M rows with high write rate

-- 16) Debugging — why is my query slow?
EXPLAIN SELECT * FROM posts WHERE MATCH(title, body) AGAINST('docker') LIMIT 20;
-- Look at the 'key' column. Should show your FULLTEXT index name.
-- 'rows' estimate should be small.

-- 17) Common bugs
--   • Forgetting to add the FULLTEXT index → falls back to slow LIKE
--   • Searching for words < ft_min_word_len → no results
--   • Forgetting that BOOLEAN mode doesn't stem → 'docker' doesn't match 'dockers'
--   • Using LIKE '%word%' instead of FULLTEXT → no index, slow on big tables
--   • Stopwords filter common terms — adjust the stopword table or use BOOLEAN +

-- 18) When you can't use FULLTEXT
-- For very small tables: LIKE 'word%' with a regular index is fine
-- For exact matches: use regular indexes + WHERE column = 'value'

ALTER TABLE posts ADD FULLTEXT(title, body);
SELECT * FROM posts
WHERE MATCH(title, body) AGAINST('mysql search' IN NATURAL LANGUAGE MODE);