MySQL Full-Text Search: Beyond LIKE % for Fuzzy Queries

MySQL Full-Text Search Overview

InnoDB's LIKE '%pattern' causes index invalidation. For keyword-based matching, full-text search uses inverted indexes instead of B+Tree indexes.

Inverted Index Structure

Full-text search relies on inverted indexes mapping words to document locations. Two variants:

inverted file index : {word, document IDs}

full inverted index : {word, (document ID, position)} — uses more space but enables proximity search.

Creating Full-Text Indexes

Supported on InnoDB/MyISAM tables for CHAR, VARCHAR, TEXT columns since MySQL 5.6.

At Table Creation

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 Existing Table

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Internally, InnoDB creates six auxiliary index tables partitioning words by first character's charset weight.

Using Full-Text Search: MATCH() AGAINST()

Syntax: MATCH(col1,col2,...) AGAINST(expr [search_modifier]) Search modifiers: IN NATURAL LANGUAGE MODE (default)

IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

IN BOOLEAN MODE

WITH QUERY EXPANSION

Natural Language Mode

Interprets search string as a human phrase. Returns relevance scores based on:

Whether word appears in document

Word frequency in document

Word frequency in indexed columns

Number of documents containing the word

Additional InnoDB filters:

Stopwords are ignored (e.g., 'for' yields relevance 0)

Token length must be between innodb_ft_min_token_size (default 3) and innodb_ft_max_token_size (default 84)

Example:

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

Using IF(MATCH(...) AGAINST(...), 1, NULL) avoids relevance sorting and runs faster.

Boolean Mode

Uses operators for precise control: + — word must exist - — word must not exist (no operator) — optional, increases relevance if present @distance — proximity search (e.g., "Pease hot"@30 within 30 bytes) > — increase relevance < — decrease relevance ~ — allow but negative relevance * — wildcard prefix (e.g., lik* matches lik, like, likes) " — 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);

Result screenshots illustrate each operator's effect.

Query Expansion Mode

Two-phase process for short queries needing implied knowledge (e.g., 'database' → also find MySQL, Oracle, RDBMS):

Phase 1: Full-text search on original keywords.

Phase 2: Search again using words extracted from phase 1 results.

Enable with WITH QUERY EXPANSION or IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION.

Example:

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

Warning: May return many irrelevant results; use cautiously.

Dropping Full-Text Indexes

DROP INDEX full_idx_name ON db_name.table_name;

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;

Conclusion

InnoDB full-text search is practical for simple search scenarios, replacing LIKE % without external dependencies. For complex search, Elasticsearch is recommended.