MySQL FULLTEXT Search

Also Known As

TL;DR

Explanation

FULLTEXT indexes enable MATCH(col) AGAINST('query') syntax. Natural language mode ranks results by relevance. Boolean mode supports operators: +required, -excluded, *wildcard, "phrase". Minimum word length (ft_min_word_len, default 4) affects which words are indexed — short words like 'PHP' are excluded unless the config is changed. InnoDB FULLTEXT is available since MySQL 5.6. For complex search needs, Elasticsearch or MeiliSearch provide better relevance tuning, tokenisation, and multilingual support.

Common Misconception

Why It Matters

Common Mistakes

• Searching for words shorter than ft_min_word_len (default 4) — they're excluded from the index silently.
• Using FULLTEXT on MyISAM tables — works, but no transactions; prefer InnoDB.
• Expecting FULLTEXT to handle typos or fuzzy matching — it doesn't without external preprocessing.

Avoid When

• Do not use MySQL FULLTEXT for large-scale or multilingual search — use Elasticsearch or MeiliSearch.
• Do not expect FULLTEXT to match words shorter than ft_min_word_len (default 4) — 'PHP' will not match.

When To Use

• Use FULLTEXT for search boxes on medium-sized datasets (under a few million rows).
• Use boolean mode when users need exact control — required terms (+), excluded terms (-), wildcards (*).

Code Examples

-- LIKE full table scan — no relevance ranking
SELECT * FROM articles WHERE body LIKE '%php security%'; -- slow, no ranking

-- Create FULLTEXT index
CREATE FULLTEXT INDEX idx_search ON articles (title, body);

-- Natural language search (relevance ranked)
SELECT id, title,
       MATCH(title, body) AGAINST('php security' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST('php security' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;

-- Boolean mode (exact control)
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+php +security -deprecated' IN BOOLEAN MODE);

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

Tags

Related Terms