Understanding Full-Text Search and Inverted Indexes in MySQL InnoDB

MySQL InnoDB supports full-text search, which allows keyword-based retrieval of documents without relying on B+Tree indexes. Full-text search is implemented using inverted indexes that map words to the documents and positions where they appear.

There are two forms of inverted indexes: the inverted file index (word → document IDs) and the full inverted index (word → document ID and position). The latter consumes 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;

Alternatively, add a full-text index to an existing table:

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Full-text indexes can be queried with the MATCH() AGAINST() syntax. The basic form is:

MATCH(col1, col2, ...) AGAINST('search terms' [search_modifier])

Search modifiers include:

IN NATURAL LANGUAGE MODE

IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

IN BOOLEAN MODE

WITH QUERY EXPANSION

Natural Language mode treats the query as a natural phrase. Example:

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

For relevance ranking:

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

Boolean mode allows operators such as + (must exist), - (must not exist), * (wildcard), " (phrase), and proximity @distance . Example:

SELECT *
FROM fts_test
WHERE MATCH(content) AGAINST('+Pease -hot' IN BOOLEAN MODE);

Other Boolean examples demonstrate optional terms, proximity search, boosting, and wildcard usage.

Query Expansion enhances natural language searches by automatically adding related terms. Example of creating an index and using expansion:

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

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

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

While query expansion can improve recall, it may also introduce irrelevant results, so it should be used cautiously.

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

The article concludes that MySQL full-text search is useful for simple search scenarios and can replace LIKE '%...%' patterns, but for complex requirements an external search engine like Elasticsearch may be more appropriate.