Master MySQL Full-Text Search: Inverted Index, Queries, and Best Practices

Introduction

InnoDB fuzzy queries using %xx invalidate indexes, which makes keyword‑based searches such as search‑engine or e‑commerce product lookups impossible with traditional B+Tree indexes. Full‑text search, designed for similarity‑based queries, solves this problem and has been supported by InnoDB since MySQL 5.6.

Inverted Index

Full‑text search relies on an inverted index, a structure similar to B+Tree but storing a mapping between words and the documents (or positions) where they appear. Two common forms are:

Inverted file index: {word, document_id} Full inverted index:

{word, (document_id, position)}

The first image shows that the word "code" appears in documents 1 and 4. The second image (full inverted index) records the exact positions of the word, enabling more precise queries at the cost of extra space.

Creating a Full‑Text Index

1. Define the index when creating a table

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 Full‑Text Search

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_modifier:
  IN NATURAL LANGUAGE MODE
  | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
  | IN BOOLEAN MODE
  | WITH QUERY EXPANSION

Natural Language Mode

The query string is interpreted as a natural‑language phrase; rows containing the specified keywords are returned.

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

Adding AS Relevance shows the relevance score, and the boolean version can be faster because it skips relevance sorting.

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

Boolean Mode

Boolean mode allows operators to control which words must appear, must not appear, or affect relevance. +: 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 ~: allow word with negative relevance *: wildcard prefix, e.g., lik* matches lik, like,

likes

""

: 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 IBM' IN BOOLEAN MODE);

SELECT * FROM fts_articles
WHERE MATCH(title, body) AGAINST('"DB2 IBM"@3' 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('MySQL ~database' IN BOOLEAN MODE);

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

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

Query Expansion

Query Expansion modifies natural‑language searches to include related terms (implicit knowledge). It runs in two stages: first, a normal full‑text search; second, a search using the terms generated from the first stage.

-- 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 with caution.

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;

Conclusion

The article combines theory and hands‑on examples to introduce MySQL full‑text indexes, covering index structures, creation, query syntax, mode variations, and index removal, providing a practical reference for developers needing keyword‑based search in InnoDB.