Full-Text Search

MySQL Full-Text Search

MySQL InnoDB full-text search uses an inverted index for efficient text queries with relevance scoring — far faster than LIKE '%keyword%' full-table scans.

Creating Full-Text Indexes

-- English
CREATE FULLTEXT INDEX ft_content ON articles (title, content);

-- Chinese (ngram tokenizer)
CREATE FULLTEXT INDEX ft_cn ON articles (title, content) WITH PARSER ngram;

Natural Language Mode

SELECT id, title,
       MATCH(title, content) AGAINST ('query optimization') AS score
FROM articles
WHERE MATCH(title, content) AGAINST ('query optimization')
ORDER BY score DESC LIMIT 10;

Boolean Mode Operators

-- + required, - excluded, * wildcard, "phrase" exact
WHERE MATCH(t,c) AGAINST ('+MySQL -deprecated "query plan"' IN BOOLEAN MODE)

Chinese Search (ngram)

ngram tokenizer (default N=2) slides a window across text: "高性能" → "高性", "性能". Set ngram_token_size=2 in my.cnf. Single-character search requires N=1 (larger index).

MySQL FTS vs Elasticsearch

Use MySQL FTS for <5M documents with simple keyword matching needs — zero extra infrastructure. Switch to Elasticsearch for intelligent Chinese tokenization, fuzzy matching, synonyms, and scale beyond 10M documents.