Mastering MySQL Full-Text Search: Inverted Index, Query Modes, and Best Practices

InnoDB fuzzy queries using "%" often invalidate indexes, so many applications such as search engines and e‑commerce sites need keyword‑based full‑text search instead of B+Tree range scans.

Inverted Index

Full‑text search relies on an inverted index, similar to a B+Tree but storing a mapping from each word to the documents that contain it. Two common forms are:

Inverted file index : {word → [docId1, docId2, …]} Full inverted index :

{word → [(docId, position), …]}

The first form stores only document IDs, making look‑ups simple; the second stores positions, consuming more space but enabling phrase and proximity searches.

Creating a Full‑Text Index

You can define a full‑text index when creating a table:

CREATE TABLE table_name (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  author VARCHAR(200),
  title VARCHAR(200),
  content TEXT(500),
  FULLTEXT full_index_name (col_name)
) ENGINE=InnoDB;

Or add it later to an existing table:

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Using a Full‑Text Index

Full‑text search works only on InnoDB or MyISAM tables and on CHAR, VARCHAR, or TEXT columns. The basic query syntax is:

MATCH(col1, col2, ...) AGAINST('expr' [IN NATURAL LANGUAGE MODE | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION | IN BOOLEAN MODE | WITH QUERY EXPANSION]);

Natural Language Mode

The query string is treated as a natural language phrase. Example:

SELECT COUNT(*) AS cnt FROM fts_articles WHERE MATCH(title, body) AGAINST('MySQL');

A faster variant uses IF to avoid relevance sorting:

SELECT COUNT(IF(MATCH(title, body) AGAINST('MySQL'), 1, NULL)) AS cnt FROM fts_articles;

Relevance is calculated from four factors: word presence, word frequency in the document, number of documents containing the word, and total occurrences of the word across the index.

Boolean Mode

Boolean mode adds operators that control required, prohibited, proximity, weighting, wildcards, and exact phrases. +: word must be present -: word must be absent @distance: two words must be within distance bytes >: increase weight <: decrease weight ~: optional with negative weight *: prefix wildcard (e.g., My* matches My, MySQL) "phrase": exact phrase match

Demo queries:

SELECT * FROM fts_articles WHERE MATCH(title, body) AGAINST('+MySQL -YourSQL' IN BOOLEAN MODE);

SELECT * FROM fts_articles WHERE MATCH(title, body) AGAINST('MySQL IBM' IN BOOLEAN MODE);

SELECT * FROM fts_articles WHERE MATCH(title, body) AGAINST('"DB2 IBM"@3' IN BOOLEAN MODE);

SELECT * FROM fts_articles WHERE MATCH(title, body) AGAINST('+MySQL +(>database <DBMS)' IN BOOLEAN MODE);

SELECT * FROM fts_articles WHERE MATCH(title, body) AGAINST('MySQL ~database' IN BOOLEAN MODE);

SELECT * FROM fts_articles WHERE MATCH(title, body) AGAINST('My*' IN BOOLEAN MODE);

SELECT * FROM fts_articles WHERE MATCH(title, body) AGAINST('"MySQL Security"' IN BOOLEAN MODE);

Query Expansion

Query Expansion (automatic relevance feedback) runs the search twice: first to collect related terms, then to re‑search with those terms. It is enabled with WITH QUERY EXPANSION or IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION.

SELECT * FROM fts_articles WHERE MATCH(title, body) AGAINST('database' WITH QUERY EXPANSION);

While it can improve recall, it may also return many irrelevant rows, so use it cautiously.

Deleting a Full‑Text Index

Indexes can be removed directly or via ALTER TABLE:

DROP INDEX full_idx_name ON db_name.table_name;

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;

Conclusion

InnoDB full‑text search is a practical solution for simple keyword searches and can replace LIKE '%…%' without external services. For more complex search requirements, dedicated engines such as Elasticsearch are recommended.