Full-Text Search
In this page:
What is a FULLTEXT Index?
A regular index only matches exact values, but a FULLTEXT index analyzes the words inside a text column so you can search for a term appearing anywhere within a longer paragraph, not just at the start of the field.
Example: What is a FULLTEXT Index?
CREATE TABLE articles (id INT, body TEXT, FULLTEXT (body)) ENGINE=InnoDB;
INSERT INTO articles VALUES (1, 'MySQL supports full text search on text columns');
SELECT * FROM articles WHERE MATCH(body) AGAINST('search');Using NATURAL LANGUAGE Mode
NATURAL LANGUAGE mode behaves like a typical search engine — it interprets your search phrase, finds rows containing matching words, and automatically ranks the results by how relevant each match is.
Example: Using NATURAL LANGUAGE Mode
CREATE TABLE articles (id INT, body TEXT, FULLTEXT (body)) ENGINE=InnoDB;
INSERT INTO articles VALUES (1, 'MySQL full text search ranks results by relevance');
SELECT *, MATCH(body) AGAINST('full text search' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles;Using BOOLEAN Mode
BOOLEAN mode gives you explicit control using special operators: prefixing a word with a plus sign requires it to appear, while a minus sign excludes rows containing that word entirely.
Example: Using BOOLEAN Mode
CREATE TABLE articles (id INT, body TEXT, FULLTEXT (body)) ENGINE=InnoDB;
INSERT INTO articles VALUES (1, 'MySQL search'), (2, 'PostgreSQL search');
SELECT * FROM articles WHERE MATCH(body) AGAINST('+search -PostgreSQL' IN BOOLEAN MODE);Query Expansion Mode
Query expansion mode is designed for short, vague searches — it runs your search once, pulls out commonly associated words from the top matches, then reruns the search including those extra terms to broaden the results.
Example: Query Expansion Mode
CREATE TABLE articles (id INT, body TEXT, FULLTEXT (body)) ENGINE=InnoDB;
INSERT INTO articles VALUES (1, 'database indexing basics');
SELECT * FROM articles WHERE MATCH(body) AGAINST('database' WITH QUERY EXPANSION);Performance and FULLTEXT Best Practices
FULLTEXT indexes add real overhead, so it's worth applying them only to columns that genuinely need text search, and you can inspect the relevance score MySQL assigns each row directly in your query results.
Example: Performance and FULLTEXT Best Practices
CREATE TABLE articles (id INT, body TEXT, FULLTEXT (body)) ENGINE=InnoDB;
INSERT INTO articles VALUES (1, 'MySQL full text search');
SELECT id, MATCH(body) AGAINST('MySQL') AS relevance_score FROM articles;Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: