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

InnoDB’s regular B‑Tree indexes cannot handle fuzzy searches using patterns like %xx, which are common in search engines and e‑commerce sites that need keyword‑based filtering; full‑text search, introduced in MySQL 5.6 for InnoDB, solves this problem.

Inverted Index

Full‑text search relies on an inverted index, a structure similar to a B‑Tree that maps each word to the documents (and positions) where it appears. Two common forms are:

Inverted file index: {word, [document_id...]} Full inverted index:

{word, [(document_id, position)...]}

The first form stores only document IDs, while the second also records the exact word position, enabling more precise queries at the cost of extra space.

Creating Full‑Text 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;

On an existing table

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Using Full‑Text Index

Full‑text queries work only on InnoDB or MyISAM tables and on 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 Mode

This mode treats the query string as a natural phrase and ranks documents by relevance.

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

Adding AGAINST(... ) AS Relevance returns the relevance score for each row.

Boolean Mode

Boolean mode supports operators that control inclusion, exclusion, weighting, proximity, and wildcards. +: word must be present -: word must be absent

(no operator): word is optional but boosts relevance if present @distance: proximity search, e.g.,

MATCH(content) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)

<

: lower relevance ~: negative relevance ": exact phrase lik*: prefix match (matches lik, like, likes, etc.)

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 Security"' IN BOOLEAN MODE);

Query Expansion

Query expansion runs the search twice: first with the original terms, then with additional related terms derived from the first result set, useful when the original query is too short.

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

Because it can return many irrelevant rows, it should be used cautiously.

Dropping 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 practice to introduce InnoDB full‑text indexes, covering inverted index structures, creation syntax, query modes (natural language, boolean, query expansion), and index removal.