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

Preface

We know that InnoDB’s use of "%xx" in fuzzy queries invalidates indexes, yet many scenarios—such as search engines or e‑commerce sites—require keyword‑based full‑text search that B+Tree indexes cannot handle efficiently.

Full‑text search (Full‑Text Search) is designed for similarity‑based queries, allowing retrieval of any information from whole documents stored in the database.

In early MySQL versions InnoDB did not support full‑text search; support began with MySQL 5.6.

Inverted Index

Full‑text search typically uses an inverted index, which, like a B+Tree, maps words to the documents (and positions) where they appear. Two common forms are:

inverted file index: {word, document IDs}

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

The first diagram shows an inverted file index where the word "code" appears in documents 1 and 4. The full inverted index stores both document IDs and positions, enabling more precise queries at the cost of extra space.

Full‑Text Search

Create 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);

Use Full‑Text Index

MySQL supports full‑text queries on InnoDB or MyISAM tables for CHAR, VARCHAR, or 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

The default mode interprets the query as a natural language phrase, returning documents containing the specified keywords.

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

The same query can be written with an IF wrapper for faster execution:

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

Relevance can also be retrieved:

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

Boolean

Boolean mode uses operators to control required, prohibited, or optional words, as well as weighting modifiers.

+ : word must be present

- : word must be absent

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

@distance : proximity search (words within a byte distance)

> : increase relevance

< : decrease relevance

~ : negative relevance

* : wildcard for word prefixes

"..." : 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 augments a natural language query with related terms (e.g., searching for "database" also considers "MySQL", "Oracle", "RDBMS"). It runs in two stages: initial full‑text search, then a second search using the terms discovered.

CREATE FULLTEXT INDEX title_body_index ON fts_articles(title, body);
SELECT * FROM fts_articles
WHERE MATCH(title, body) AGAINST('database');
SELECT * FROM fts_articles
WHERE MATCH(title, body) AGAINST('database' WITH QUERY EXPANSION);

While powerful, query expansion can introduce irrelevant results and should be used cautiously.

Delete Full‑Text Index

1. Drop the index directly: DROP INDEX full_idx_name ON db_name.table_name; 2. Or use ALTER TABLE:

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;

Conclusion

This article combined theory and practice to introduce MySQL full‑text indexes; readers interested in MySQL can follow the MySQL column for more details.