Master MySQL InnoDB Full-Text Search: Indexes, Queries, and Advanced Techniques

Introduction

MySQL’s InnoDB engine supports full‑text search starting from version 5.6. Unlike simple %xx% pattern matching, which disables index usage, full‑text search uses an inverted index to enable efficient keyword‑based queries on large text columns such as VARCHAR and TEXT.

Inverted Index

Full‑text search relies on an inverted index, similar in concept to a B‑Tree but storing a mapping from each word to the documents (or positions) where it appears. Two common forms are:

Inverted file index : {word → list of document IDs} Full inverted index : {word → list of (document ID, position) tuples} The following diagram shows an inverted file index where the word "code" appears in documents 1 and 4.

A full inverted index also records the exact positions of each occurrence, allowing more precise ranking and additional search features.

Creating a Full‑Text Index

When creating a table, you can define a full‑text index directly:

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;

Alternatively, add an index to an existing table:

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Using Full‑Text Search

Full‑text queries use the MATCH() function together with AGAINST(). The syntax is:

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

Supported search_modifier values include:

IN NATURAL LANGUAGE MODE

IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

IN BOOLEAN MODE

WITH QUERY EXPANSION

Full‑text search works only on CHAR, VARCHAR, or TEXT columns of InnoDB or MyISAM tables.

Natural Language Mode

Interprets the query as a natural phrase. Example:

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

This returns the number of rows where title or body contains the word “MySQL”.

Boolean Mode

Allows operators to control required, prohibited, or weighted terms. Common operators: +: word must be present -: word must be absent (no operator): optional but boosts relevance @distance: proximity search (words within distance bytes) >: increase relevance <: decrease relevance ~: allow word with negative relevance *: wildcard prefix ": exact phrase

Demo queries:

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

-- Demo 2: no operator
SELECT * FROM `fts_articles`
WHERE MATCH(title, body) AGAINST('MySQL IBM' IN BOOLEAN MODE);

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

-- Demo 4: > <
SELECT * FROM `fts_articles`
WHERE MATCH(title, body) AGAINST('+MySQL +(>database <DBMS)' IN BOOLEAN MODE);

-- Demo 5: ~
SELECT * FROM `fts_articles`
WHERE MATCH(title, body) AGAINST('MySQL ~database' IN BOOLEAN MODE);

-- Demo 6: *
SELECT * FROM `fts_articles`
WHERE MATCH(title, body) AGAINST('My*' IN BOOLEAN MODE);

-- Demo 7: "
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. It runs in two stages: first the original query, then a second search using terms extracted from the first result set. Example:

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

While query expansion can improve recall, it may also introduce irrelevant results, so it should be used with caution.

Deleting a Full‑Text Index

Two ways to remove 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;

These statements cleanly drop the index without affecting the underlying table data.