Understanding MySQL InnoDB Full-Text Search and Inverted Indexes

We all know that using %xx in InnoDB fuzzy queries invalidates indexes, but many scenarios require keyword‑based searches such as search engines or e‑commerce sites.

While numeric comparisons and range filters handle most queries, keyword matching needs similarity‑based queries, which is what full‑text search provides.

Full‑text search (FTS) allows retrieving any information from whole books or articles stored in a database, supporting statistical analysis and various query modes.

In early MySQL versions InnoDB did not support FTS; support was added starting with MySQL 5.6.

Inverted Index

FTS usually relies on an inverted index, a structure similar to a B+Tree that maps words to the documents (and positions) where they appear. Two common forms are:

inverted file index : {word, list of document IDs}

full inverted index : {word, (document ID, position) …}

The inverted file index stores only the document IDs for each word, while the full inverted index also records the exact position of the word within each document, enabling more precise queries.

Full‑Text Search

Creating a Full‑Text Index

1. Create a table with a full‑text 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;

2. Add a full‑text index to an existing table

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Using a Full‑Text Index

MySQL supports full‑text queries on InnoDB or MyISAM tables for CHAR, VARCHAR, and TEXT columns. The basic syntax is:

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

Search modifiers include NATURAL LANGUAGE MODE, BOOLEAN MODE, and WITH QUERY EXPANSION.

Natural Language Mode

Interprets the query string as a natural‑language phrase. Example:

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

The same query can be written to improve performance by avoiding relevance sorting:

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

You can also retrieve the relevance score:

SELECT *,
       MATCH(title, body) AGAINST('MySQL') AS Relevance
FROM fts_articles;

Relevance is calculated based on word presence, frequency, document frequency, and the number of documents containing the word.

Boolean Mode

Allows operators such as + (must), - (must not), >, <, ~, *, and quotation marks for phrase search. Example requiring rows that contain "MySQL" but not "YourSQL":

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

Other operator examples:

+ : word must be present

- : word must be absent

(no operator) : word is optional but boosts relevance if present

@distance : proximity search, e.g., MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)

> : increase relevance

< : decrease relevance

~ : negative relevance if present

* : wildcard prefix, e.g., My*

" : exact phrase

Demo queries illustrate each operator.

Query Expansion

Extends a natural‑language query with implied knowledge, useful when the keyword is short. It is enabled with WITH QUERY EXPANSION (also called blind query expansion or automatic relevance feedback).

-- create index
CREATE FULLTEXT INDEX title_body_index ON fts_articles(title, body);

-- natural language query
SELECT *
FROM `fts_articles`
WHERE MATCH(title, body) AGAINST('database');

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

Query expansion can return many non‑relevant rows, so it should be used cautiously.

Deleting a Full‑Text Index

1. Drop index directly

DROP INDEX full_idx_name ON db_name.table_name;

2. Use ALTER TABLE

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;