MySQL Fuzzy Search Without LIKE%: Using Full‑Text Indexes

InnoDB's pattern matching with LIKE '%xx' disables index usage; full‑text search offers an alternative for keyword‑based fuzzy queries.

Full‑text search relies on an inverted index. Two forms are described: inverted file index (word → document IDs) and full inverted index (word → document ID + position). The following images illustrate these structures.

Creating a full‑text index in MySQL (supported since 5.6) can be done when the table is created or later with ALTER TABLE. Example syntax:

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

Or add an index to an existing table:

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Full‑text queries use MATCH(col1, col2, ...) AGAINST(expr [search_modifier]). Three search modifiers are supported:

Natural Language Mode

Natural Language Mode with Query Expansion

Boolean Mode

Natural Language Mode example:

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

Another form that returns relevance scores:

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

Relevance is calculated from four factors: word presence, frequency, document frequency, and total document count.

Stop‑words and token‑size limits affect results. For example, searching for the stop‑word 'for' yields zero relevance:

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

Boolean Mode supports operators such as + (must exist), - (must not exist), @distance, >, <, ~, *, and quoted phrases. Demo queries:

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

Query without operators (optional terms):

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

Proximity search using @distance (words within 3 bytes):

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

Weighting with > and <:

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

Negating relevance with ~:

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

Wildcard prefix search:

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

Exact phrase search:

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

Query Expansion runs a two‑stage search to broaden results. First stage performs a normal full‑text lookup; the second stage re‑searches using terms extracted from the first stage. Example before and after screenshots are described in the article.

Creating the index:

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

Natural Language query:

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

With Query Expansion:

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

Because Query Expansion may return many non‑relevant rows, it should be used with caution.

Dropping a full‑text index can be done 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;

In summary, MySQL InnoDB full‑text search is practical for simple keyword searches and can replace LIKE '%...%' without external services. For more complex search requirements, dedicated engines such as Elasticsearch are recommended.