Master MySQL Full‑Text Search: Inverted Index, Query Modes, and Practical Examples

InnoDB’s fuzzy queries using patterns like %xx invalidate indexes, so many applications need full‑text search to match keywords instead of exact numeric comparisons. MySQL added native InnoDB full‑text support in version 5.6, using an inverted index structure similar to a B+Tree.

Inverted Index Basics

Full‑text search stores a mapping between each word and the documents (or positions within documents) where it appears. Two common forms are:

Inverted file index : {word → [doc_id, …]} Full inverted index : {word → [(doc_id, position), …]} These structures enable fast keyword look‑ups and positional queries, as illustrated in the accompanying diagrams.

Creating a Full‑Text Index

You can define a full‑text index when creating a table or add it later.

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 an index to an existing table:

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Using Full‑Text Search

MySQL queries full‑text columns with the MATCH(... ) AGAINST(... ) function. The basic syntax is:

MATCH(col1, col2, ...) AGAINST(expr [search_modifier])

Supported search_modifier values are:

IN NATURAL LANGUAGE MODE

IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

IN BOOLEAN MODE

WITH QUERY EXPANSION

Natural Language Mode

This is the default mode; the query string is interpreted as a natural‑language phrase. Example:

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

The query returns the number of rows containing the word “MySQL”.

Boolean Mode

Boolean mode allows operators to control required, prohibited, or weighted terms: + – term must be present - – term must be absent (no operator) – optional term that boosts relevance if present @distance – proximity search (e.g., "phrase"@30) > – increase term weight < – decrease term weight ~ – term is allowed but contributes negative relevance * – wildcard for word prefixes (e.g., My*) "…" – exact phrase match

Demo queries:

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

Find rows containing “MySQL” but not “YourSQL”.

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

Both terms are optional; rows containing either term rank higher.

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

Requires “DB2” and “IBM” to appear within three bytes of each other.

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

Rows must contain “MySQL” and “database”; the presence of “DBMS” lowers relevance.

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

Wildcard search for words starting with “My”.

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

Exact phrase match.

Relevance Calculation

MySQL computes relevance based on four factors:

Whether the word appears in the document

How many times the word appears

How many rows contain the word (inverse document frequency)

Total number of words in the indexed column

Stopwords and Token Size

Words listed in the stopword table are ignored. The engine also respects the length limits set by innodb_ft_min_token_size (default 3) and innodb_ft_max_token_size (default 84). Tokens shorter than the minimum or longer than the maximum are excluded from the index.

Query Expansion

Query expansion runs the original query, extracts the most significant terms, and re‑executes the search with those terms added. It is useful when the original keyword set is too short.

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

The result set is broader, including rows that contain related terms such as “MySQL”, “Oracle”, or “RDBMS”. Use this mode cautiously, as it can introduce irrelevant matches.

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 provides a lightweight solution for keyword‑based queries, eliminating the need for LIKE '%…%' patterns and avoiding external search services for simple use‑cases. For more complex search requirements—such as advanced ranking, faceting, or massive data volumes—dedicated engines like Elasticsearch remain the preferred choice.