MySQL InnoDB Full‑Text Search: Inverted Index, Query Modes, and Practical Examples

InnoDB’s pattern matching with %xx disables normal B‑Tree indexes, so keyword‑based searches require full‑text search, which is useful for search engines and e‑commerce sites that need to match words inside large text fields.

Full‑text search relies on an inverted index. Two common forms are the inverted file index (mapping a word to document IDs) and the full inverted index (mapping a word to document IDs and the exact positions within each document). The article illustrates these structures with diagrams.

Creating a full‑text index in MySQL can be done when the table is created or later on an existing table. Example syntax for a new 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;

For an existing table:

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Full‑text queries use the MATCH(... ) AGAINST(... ) construct with optional modifiers:

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

where search_modifier can be IN NATURAL LANGUAGE MODE , IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION , IN BOOLEAN MODE , or WITH QUERY EXPANSION .

The article details three search modes:

Natural Language : treats the query as a human phrase; the engine returns rows that contain the keywords, ranking by relevance.

Boolean : uses operators such as + (must contain), - (must not contain), (no operator) (optional but boosts relevance), @distance (proximity), > / < (increase/decrease weight), ~ (negative weight), * (wildcard prefix), and " (exact phrase). Example:

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

Other demos illustrate no‑operator queries, proximity searches ( "DB2 IBM"@3 ), weight modifiers ( '+MySQL +(>database <DBMS)' ), negative weight ( 'MySQL ~database' ), wildcard ( 'My*' ), and exact phrase ( '"MySQL Security"' ).

Query Expansion extends natural‑language searches by automatically adding related terms (e.g., searching for database also matches MySQL , Oracle , RDBMS ). The article shows how to enable it with WITH QUERY EXPANSION and warns about possible loss of precision.

Finally, the article explains how to delete a full‑text index either directly:

DROP INDEX full_idx_name ON db_name.table_name;

or via ALTER TABLE :

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;