Mastering MySQL Full‑Text Search: Index Types, Queries, and Best Practices

MySQL InnoDB supports full‑text search (FTS) starting from version 5.6, allowing keyword‑based queries that overcome the limitations of B‑Tree indexes for fuzzy matching. Full‑text search relies on an inverted index , which maps each word to the documents (or rows) containing it. Two forms exist:

Inverted file index : stores {word, document_id}.

Full inverted index : stores {word, (document_id, position)}, enabling precise location tracking.

Creating a full‑text index can be done at table creation or later:

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 it to an existing table:

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Full‑text queries use the MATCH(... ) AGAINST(... ) syntax. The AGAINST clause can include a search modifier: IN NATURAL LANGUAGE MODE (default) – treats the query as a natural phrase. IN BOOLEAN MODE – allows operators such as + (must), - (must not), * (wildcard), "" (phrase), @distance (proximity), > / < (boost/reduce relevance), and ~ (negative relevance). WITH QUERY EXPANSION – runs a second pass using terms automatically derived from the initial results, useful for short queries.

Examples:

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

Using IN BOOLEAN MODE with operators:

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

Proximity search:

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

Boosting and reducing relevance:

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

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

Relevance can be retrieved directly:

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

Relevance calculation considers four factors: word presence, word frequency in the document, word frequency in the indexed column, and the number of documents containing the word.

InnoDB FTS respects stopwords and token size limits ( innodb_ft_min_token_size default 3, innodb_ft_max_token_size default 84). Words in the stopword list are ignored, and tokens outside the size range are excluded from indexing.

To delete 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;

In summary, InnoDB full‑text search provides a lightweight alternative to external search engines for simple keyword queries, supporting various query modes and relevance tuning, while more complex search scenarios may still require dedicated engines like Elasticsearch.