MySQL Full‑Text Search: Inverted Index, Query Modes, and Usage

When using the %xx wildcard in InnoDB fuzzy queries, the index becomes ineffective, which makes full‑text search necessary for keyword‑based filtering such as search engines or e‑commerce product descriptions.

Full‑text search relies on an inverted index, similar to a B+Tree, that maps words to the documents (or rows) where they appear. Two common representations are:

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

full inverted index : {word, list of (document ID, position) pairs}

In MySQL 5.6 and later, InnoDB supports full‑text indexes. The following SQL creates 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;

To add a full‑text index to an existing table:

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Full‑text queries use the MATCH() and AGAINST() functions. The basic syntax is:

MATCH(col1, col2, ...) AGAINST('search_string' [search_modifier])

The supported search modifiers are:

IN NATURAL LANGUAGE MODE

IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

IN BOOLEAN MODE

WITH QUERY EXPANSION

Natural Language Mode

Interprets the search string as a natural phrase. Example:

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

Another form returns relevance scores:

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

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

Boolean Mode

Allows explicit operators to control matching:

+ – word must be present

- – word must be absent

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

@distance – proximity search (words within distance bytes)

> – increase relevance

< – decrease relevance

~ – allow word but give negative relevance

* – wildcard for word prefix

" – exact phrase

Examples:

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

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

Query Expansion

Extends a natural‑language query by automatically adding related terms (blind query expansion). It is enabled with the WITH QUERY EXPANSION clause:

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

While it can improve recall, it may also introduce irrelevant results, so it should be used cautiously.

Deleting a Full‑Text Index

Two ways to drop a full‑text index:

DROP INDEX full_idx_name ON db_name.table_name;

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;

Additional considerations include stopwords (words ignored during indexing) and token size limits controlled by innodb_ft_min_token_size (default 3) and innodb_ft_max_token_size (default 84).

The article concludes with a reminder that the content is intended for technical discussion and not for advertising or recruitment.