Eliminate LIKE% in MySQL: Use Full‑Text Search for Efficient Fuzzy Queries

InnoDB’s traditional fuzzy queries using LIKE '%keyword%' cause index loss, which is unsuitable for search‑engine‑style keyword lookups or e‑commerce product description searches. Since MySQL 5.6, InnoDB supports full‑text search, which relies on an inverted index to locate words efficiently.

The inverted index stores a mapping between each word and the documents (or rows) that contain it. Two forms are shown: an inverted file index ({word, document‑ids}) and a full inverted index ({word, (document‑id, position)}). The latter uses more space but enables precise location and advanced search features.

Creating a 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;

After the table exists, add a full‑text index with:

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Using the index

Full‑text queries use MATCH(... ) AGAINST(... ). Three search modifiers are available:

Natural Language : default mode, interprets the query as a human phrase.

Boolean : allows operators such as + (must exist), - (must not exist), > (increase relevance), < (decrease relevance), ~ (negative relevance), @distance (proximity), wildcards word*, and quoted phrases.

Query Expansion : runs a second search using terms automatically derived from the first pass, useful when the original keyword is too short.

Example of a natural‑language query:

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

Boolean example that requires MySQL but excludes YourSQL:

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

Proximity search (words within 30 bytes):

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

Wildcard search for words starting with My:

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

Relevance is calculated based on four factors: word presence, word frequency in the document, word frequency in the indexed column, and the number of documents containing the word.

InnoDB also respects stopwords (e.g., the word for is ignored) and token‑size limits controlled by innodb_ft_min_token_size (default 3) and innodb_ft_max_token_size (default 84). Words outside this range are excluded from the search.

Query Expansion syntax:

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

Because query expansion can introduce many irrelevant results, it should be used with caution.

Removing a full‑text index DROP INDEX full_idx_name ON db_name.table_name; or

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;

These commands delete the full‑text index without affecting the table data.

Overall, the article walks through the problem of inefficient LIKE% searches, explains the underlying inverted‑index mechanism, demonstrates how to create, query, and fine‑tune full‑text indexes in MySQL, and shows how to safely remove them when no longer needed.