Back to Tutorials
TECHNICAL TUTORIAL

MySQL Full-Text Search.

Create FULLTEXT indexes and compare search plans with LIKE.

MySQL SQL Dapper

MySQL FULLTEXT search

1. Create an index

ALTER TABLE Products ADD FULLTEXT INDEX FT_Products_Search (Title, Description);

2. Search with relevance

SELECT Id, Title,
       MATCH(Title, Description) AGAINST (@Search IN NATURAL LANGUAGE MODE) AS Score
FROM Products
WHERE MATCH(Title, Description) AGAINST (@Search IN NATURAL LANGUAGE MODE)
ORDER BY Score DESC
LIMIT 20;

3. Compare alternatives

EXPLAIN ANALYZE SELECT Id FROM Products WHERE Title LIKE '%metal%';
EXPLAIN ANALYZE SELECT Id FROM Products
WHERE MATCH(Title, Description) AGAINST ('metal' IN NATURAL LANGUAGE MODE);

Run against representative data and compare actual execution time, rows examined, and result quality. FULLTEXT token-size settings and stopwords affect short terms; changing token size requires rebuilding indexes. Consider ngram parsing for suitable languages or a dedicated search engine for fuzzy matching.

Back to Tutorials