Full-Text Search Indexes (MATCH ... AGAINST)0%

Execution Plan Analysis with EXPLAIN & EXPLAIN ANALYZE

Full-Text Search Indexes (MATCH ... AGAINST)

Advanced13 min read•Updated: 2026-09-12

Full-Text Search Indexes (MATCH ... AGAINST)

Standard B+Tree indexes are incapable of efficiently searching for keywords inside unstructured text (e.g., blog posts, product reviews, or article bodies). Using LIKE '%keyword%' forces a full table scan that cripples database performance.

MySQL provides Full-Text Indexes (FTS) powered by an internal Inverted Index, queried using the MATCH() ... AGAINST() syntax.

How Inverted Indexes Work

Instead of mapping rows to words, a Full-Text Inverted Index tokenizes text into distinct words (tokens) and maps each word to a list of Document IDs where it appears:

1Token "database" ──> Appears in Doc #1, Doc #4, Doc #89
2Token "relational" ──> Appears in Doc #4, Doc #12

Looking up "database" requires only a single index lookup to find all matching rows instantly!

Creating a Full-Text Index

1CREATE TABLE articles (
2    article_id INT AUTO_INCREMENT PRIMARY KEY,
3    title VARCHAR(200) NOT NULL,
4    body TEXT NOT NULL,
5    FULLTEXT INDEX ft_title_body (title, body)
6) ENGINE = InnoDB;

1. Natural Language Mode (Default)

Natural Language Mode calculates a relevance score based on word frequency and uniqueness:

1SELECT 
2    article_id, 
3    title,
4    MATCH(title, body) AGAINST('mysql performance tuning') AS relevance_score
5FROM articles
6WHERE MATCH(title, body) AGAINST('mysql performance tuning' IN NATURAL LANGUAGE MODE)
7ORDER BY relevance_score DESC;

2. Boolean Mode: Power Search with Operators

Boolean Mode allows precision keyword filtering using search operators:

Operator	Meaning	Example
`+`	Word MUST be present	`+mysql +optimization`
`-`	Word MUST NOT be present	`+mysql -oracle`
``*	Wildcard / prefix operator	`auto*` (matches automates, automatic)
`""`	Exact phrase search	`"query execution plan"`
`( )`	Grouping expressions	`+mysql +(replication OR cluster)`

1-- Find articles mentioning 'mysql' and 'optimization', but NOT 'windows'
2SELECT article_id, title 
3FROM articles
4WHERE MATCH(title, body) AGAINST('+mysql +optimization -windows' IN BOOLEAN MODE);
5 
6-- Exact phrase match
7SELECT article_id, title 
8FROM articles
9WHERE MATCH(title, body) AGAINST('"high availability"' IN BOOLEAN MODE);

Minimum Word Length & Stopwords

By default:

Words shorter than 3 characters (in InnoDB, controlled by innodb_ft_min_token_size) are ignored.
Common English "stopwords" (like "the", "and", "is") are filtered out automatically.

1-- Inspect minimum token length
2SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What data structure powers MySQL Full-Text Search?

Question 2

Which SQL syntax is used to query a Full-Text index?

Question 3

In Boolean Mode, what does the operator + signify (e.g., +mysql)?

Question 4

How do you perform an exact multi-word phrase search in Boolean Mode?

Question 5

Why is LIKE '%keyword%' unsuitable for searching large textual corpuses?

Next Lesson

Execution Plan Analysis with EXPLAIN & EXPLAIN ANALYZE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Covering Indexes & Index Condition Pushdown (ICP)	Execution Plan Analysis with EXPLAIN & EXPLAIN ANALYZE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Execution Plan Analysis with EXPLAIN & EXPLAIN ANALYZE

Full-Text Search Indexes (MATCH ... AGAINST)

Advanced13 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Full-Text Search Indexes (MATCH ... AGAINST)

MySQL provides Full-Text Indexes (FTS) powered by an internal Inverted Index, queried using the MATCH() ... AGAINST() syntax.

How Inverted Indexes Work

Instead of mapping rows to words, a Full-Text Inverted Index tokenizes text into distinct words (tokens) and maps each word to a list of Document IDs where it appears:

1Token "database" ──> Appears in Doc #1, Doc #4, Doc #89
2Token "relational" ──> Appears in Doc #4, Doc #12

Looking up "database" requires only a single index lookup to find all matching rows instantly!

Creating a Full-Text Index

1CREATE TABLE articles (
2    article_id INT AUTO_INCREMENT PRIMARY KEY,
3    title VARCHAR(200) NOT NULL,
4    body TEXT NOT NULL,
5    FULLTEXT INDEX ft_title_body (title, body)
6) ENGINE = InnoDB;

1. Natural Language Mode (Default)

Natural Language Mode calculates a relevance score based on word frequency and uniqueness:

1SELECT 
2    article_id, 
3    title,
4    MATCH(title, body) AGAINST('mysql performance tuning') AS relevance_score
5FROM articles
6WHERE MATCH(title, body) AGAINST('mysql performance tuning' IN NATURAL LANGUAGE MODE)
7ORDER BY relevance_score DESC;

2. Boolean Mode: Power Search with Operators

Boolean Mode allows precision keyword filtering using search operators:

Operator	Meaning	Example
`+`	Word MUST be present	`+mysql +optimization`
`-`	Word MUST NOT be present	`+mysql -oracle`
``*	Wildcard / prefix operator	`auto*` (matches automates, automatic)
`""`	Exact phrase search	`"query execution plan"`
`( )`	Grouping expressions	`+mysql +(replication OR cluster)`

1-- Find articles mentioning 'mysql' and 'optimization', but NOT 'windows'
2SELECT article_id, title 
3FROM articles
4WHERE MATCH(title, body) AGAINST('+mysql +optimization -windows' IN BOOLEAN MODE);
5 
6-- Exact phrase match
7SELECT article_id, title 
8FROM articles
9WHERE MATCH(title, body) AGAINST('"high availability"' IN BOOLEAN MODE);

Minimum Word Length & Stopwords

By default:

Words shorter than 3 characters (in InnoDB, controlled by innodb_ft_min_token_size) are ignored.
Common English "stopwords" (like "the", "and", "is") are filtered out automatically.

1-- Inspect minimum token length
2SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What data structure powers MySQL Full-Text Search?

Question 2

Which SQL syntax is used to query a Full-Text index?

Question 3

In Boolean Mode, what does the operator + signify (e.g., +mysql)?

Question 4

How do you perform an exact multi-word phrase search in Boolean Mode?

Question 5

Why is LIKE '%keyword%' unsuitable for searching large textual corpuses?

Next Lesson

Execution Plan Analysis with EXPLAIN & EXPLAIN ANALYZE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Covering Indexes & Index Condition Pushdown (ICP)	Execution Plan Analysis with EXPLAIN & EXPLAIN ANALYZE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next