Master MySQL Full-Text Search: Inverted Indexes, Queries, and Best Practices

Inverted Index

Full‑text search usually relies on an inverted index, which maps words to the documents (or positions) where they appear. Two common forms are:

inverted file index: {word, document IDs}

full inverted index: {word, (document ID, position)}

The inverted file index stores each word with the IDs of documents containing it, enabling quick retrieval of matching documents. The full inverted index also records the exact position of each word within a document, using more space but allowing precise location‑based queries.

Full‑Text Search

Create Full‑Text Index

1. Create a table with a full‑text index:

<code>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;</code>

2. Add a full‑text index to an existing table:

<code>CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);</code>

Using Full‑Text Index

MySQL supports full‑text queries on InnoDB or MyISAM tables for CHAR, VARCHAR, and TEXT columns using the MATCH() AGAINST() syntax.

<code>MATCH(col1,col2,...) AGAINST(expr [search_modifier])</code>

Search modifiers include:

IN NATURAL LANGUAGE MODE

IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

IN BOOLEAN MODE

WITH QUERY EXPANSION

Natural Language

Natural language mode interprets the query as a phrase in human language.

<code>SELECT count(*) AS count FROM `fts_articles` WHERE MATCH (title, body) AGAINST ('MySQL');</code>

<code>SELECT count(IF(MATCH (title, body) AGAINST ('MySQL'), 1, NULL)) AS count FROM `fts_articles`;</code>

You can also retrieve relevance scores:

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

Boolean

Boolean mode uses operators to control word presence and relevance.

+ : word must be present

- : word must be absent

(no operator) : word is optional but boosts relevance if present

@distance : proximity search (e.g., MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE) )

* : wildcard for word prefixes

"..." : exact phrase

Examples:

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

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

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

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

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

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

Query Expansion

Query expansion modifies natural language search to include related terms when the original keyword is short or ambiguous.

<code>-- Create 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);</code>

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

Delete Full‑Text Index

1. Drop the index directly:

<code>DROP INDEX full_idx_name ON db_name.table_name;</code>

2. Use ALTER TABLE:

<code>ALTER TABLE db_name.table_name DROP INDEX full_idx_name;</code>

Conclusion

This article combined theory and practice to introduce MySQL full‑text indexes, covering their structure, creation, query modes, advanced operators, query expansion, and removal.