Master MySQL Full-Text Search: Inverted Indexes, Query Modes, and Optimization

Preface

InnoDB uses the %xx pattern for fuzzy queries, which disables index usage, but many applications require keyword-based searches such as search engines or e‑commerce sites. Full-text indexes are designed for these scenarios.

Inverted Index

Full-text search relies on an inverted index, similar to a B+Tree, storing a mapping between words and the documents (or positions) where they appear. Two forms exist: inverted file index: {word, document IDs} full inverted index: {word, (document ID, position)}

These structures enable efficient keyword lookups.

Compared to the inverted file index, the full inverted index consumes more space but provides precise location data and supports additional search features.

Full-Text Search

Create Full-Text Index

1. Create table with 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;

Example query:

SELECT table_id, name, space FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE name LIKE 'test/%';

2. Add full-text index to existing table

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Using Full-Text Index

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

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

Search modifiers include:

IN NATURAL LANGUAGE MODE

IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

IN BOOLEAN MODE

WITH QUERY EXPANSION

Natural Language

This mode treats the search string as a natural phrase. Example:

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

Another form that returns relevance:

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

Boolean mode uses operators to control term requirements: +: term must be present -: term must be absent

(no operator): term is optional but boosts relevance if present @distance: proximity search within the given byte distance >: increase relevance <: decrease relevance ~: term is allowed but reduces relevance *: wildcard for term prefix "phrase": exact phrase match

Demo queries:

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

Result: rows containing MySQL but not YourSQL.

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

Result: rows containing MySQL or IBM, with higher relevance if both appear.

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

Result: DB2 and IBM within three bytes of each other.

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

Result: rows containing MySQL and database, with higher relevance when DBMS is absent.

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

Result: rows with MySQL; relevance is reduced if database also appears.

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

Result: rows where the term starts with My.

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

Result: rows containing the exact phrase MySQL Security.

Query Expansion

Query expansion augments short queries with related terms (blind query expansion). Use WITH QUERY EXPANSION or IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION. Example:

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

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

Result without expansion:

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

Result with expansion (more matches):

Use query expansion cautiously as it may introduce irrelevant results.

Delete 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;