MySQL FULLTEXT Search
debt(d5/e3/b3/t5)
Closest to 'specialist tool catches it' (d5), because the detection_hints list Semgrep as the tool for catching the LIKE '%keyword%' pattern on TEXT/VARCHAR columns — this is a SAST-level tool, not a default linter, meaning it requires deliberate setup to detect the misuse.
Closest to 'simple parameterised fix' (e3), grounded in the quick_fix which says 'Create a FULLTEXT index on searchable columns and use MATCH...AGAINST instead of LIKE '%keyword%'' — this is slightly more than a one-line swap (requires an ALTER TABLE to add the index plus query replacement) but is still a localised change within one component rather than a cross-cutting refactor.
Closest to 'localised tax' (b3), because the choice applies only to web contexts (per applies_to) and the misconception/common_mistakes are specific to search box functionality. Misuse (staying with LIKE scans) taxes only the search-related queries/components, not the broader codebase or architecture.
Closest to 'notable trap' (t5), because the misconception states developers believe MySQL FULLTEXT is equivalent to Elasticsearch — a documented gotcha that many developers eventually learn. Additionally, common_mistakes include the silent exclusion of short words (ft_min_word_len) and no fuzzy matching, both of which are non-obvious behaviours that contradict reasonable expectations.
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);