Understanding Full-Text Search and Inverted Indexes in MySQL InnoDB

MySQL InnoDB supports full‑text search starting from version 5.6, allowing keyword‑based queries that go beyond simple numeric comparisons. Full‑text search relies on inverted indexes, which map words to the documents (or rows) where they appear, either as a simple inverted file index (word → document IDs) or a full inverted index (word → document IDs + positions).

Creating a full‑text index can be done when defining a table or later on an existing table. Example table creation with a FULLTEXT index:

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;

To add a FULLTEXT index to an existing table:

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Full‑text queries use the MATCH(... ) AGAINST(... ) syntax. The AGAINST clause can include optional modifiers to select the search mode:

MATCH(col1, col2, ...) AGAINST('search terms' IN NATURAL LANGUAGE MODE)
MATCH(col1, col2, ...) AGAINST('search terms' IN BOOLEAN MODE)
MATCH(col1, col2, ...) AGAINST('search terms' WITH QUERY EXPANSION)

Three main search modes are covered:

Natural Language : treats the query as a human language phrase; relevance is calculated automatically.

Boolean : allows operators such as + (must contain), - (must not contain), ~ (reduce relevance), * (wildcard), " (phrase), and proximity @distance .

Query Expansion : runs a second search based on terms related to the original query, useful when the initial keywords are too short.

Examples of Boolean mode 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('"MySQL Security"' 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('My*' IN BOOLEAN MODE);

Relevance calculation considers whether a word appears, its frequency, the number of rows containing the word, and the total number of rows containing the word. Stopwords and token size limits ( innodb_ft_min_token_size , innodb_ft_max_token_size ) affect which terms are indexed.

To delete a full‑text index you can either drop it directly:

DROP INDEX full_idx_name ON db_name.table_name;

or use ALTER TABLE :

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;

The article concludes with a reminder that while query expansion can improve recall, it may also introduce irrelevant results, so it should be used with caution.