Understanding MySQL InnoDB Full-Text Search and Inverted Indexes

In many applications such as search engines or e‑commerce sites, keyword‑based queries are required, which cannot be satisfied by traditional B+Tree indexes; full‑text search is designed for these scenarios.

MySQL added full‑text search support to InnoDB starting with version 5.6. The underlying structure is an inverted index , which can be either an inverted file index (word → document IDs) or a full inverted index (word → document ID and position).

Creating a full‑text index can be done when the table is created or later with CREATE 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;

Or on an existing table:

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Full‑text queries use the MATCH(... ) AGAINST(... ) syntax. MySQL supports three search modes:

Natural Language : interprets the query as a human phrase.

Boolean : allows operators such as + (must), - (must not), * (wildcard), " (phrase), @distance (proximity), etc.

Query Expansion : runs a second search using terms related to the original query.

Examples of natural language search:

SELECT * FROM `fts_articles`
WHERE MATCH(title, body) AGAINST('MySQL');

Boolean mode examples:

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

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

Query expansion example:

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

When using full‑text search, MySQL respects stopwords and token size limits ( innodb_ft_min_token_size default 3, innodb_ft_max_token_size default 84). Words that are stopwords (e.g., "for") are ignored, and words outside the token size range are not indexed.

To remove 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;