/mysql

Fulltext Indexes

Fulltext indexes are useful for keyword text search in MySQL. For advanced ranking, fuzzy matching, or complex document search, prefer a dedicated search engine.

ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);

-- Natural language (default, sorted by relevance)
SELECT *, MATCH(title, body) AGAINST('database performance') AS score
FROM articles WHERE MATCH(title, body) AGAINST('database performance');

-- Boolean mode: + required, - excluded, * suffix wildcard, "exact phrase"
WHERE MATCH(title, body) AGAINST('+mysql -postgres +optim*' IN BOOLEAN MODE);

Key Gotchas

• Min word length: default 3 chars (innodb_ft_min_token_size). Shorter words are ignored. Changing this requires rebuilding the FULLTEXT index (drop/recreate) to take effect.
• Stopwords: common words excluded. Control stopwords with innodb_ft_enable_stopword and customize via innodb_ft_user_stopword_table / innodb_ft_server_stopword_table (set before creating the index, then rebuild to apply changes).
• No partial matching: unlike LIKE '%term%', requires whole tokens (except * in boolean mode).
• MATCH() columns must correspond to an index definition: MATCH(title, body) needs a FULLTEXT index that covers the same column set (e.g. (title, body)).
• Boolean mode without required terms (no leading +) can match a very large portion of the index and be slow.
• Fulltext adds write overhead — consider Elasticsearch/Meilisearch for complex search needs.