Lesson 26 of the SQL Optimization Course: Mastering MySQL Full-Text Indexing

Introduction

For relational databases, the design of tables and SQL is written are particularly crucial. It wouldn’t be an exaggeration to say that they account for 90% of performance. So this time, specifically targeting these two major knowledge areas, we’ll conduct a detailed analysis for you, peeling back the layers.

This Series uses plain and understandable language and selects a large number of examples to elaborate on the subtleties for you.

🧑‍💻 Target audience:

DBA
Database developers
Students

We will use MySQL as the demonstration database.

Introduction & Basic Syntax

The previous article introduced the fundamental principles of full-text indexing. This article focuses on practical implementation strategies.

Comparison of Basic Syntax:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- Basic Retrieval (Field-Based Filtering)
SELECT * FROM tb1 WHERE id IN (1,2);
SELECT * FROM tb1 WHERE id < 10;

-- Full-Text Search (Content-Based Matching)
MATCH (col1, col2, ...) AGAINST (expr [search_modifier])
-- Supported Modes:
-- IN NATURAL LANGUAGE MODE  
-- IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION  
-- IN BOOLEAN MODE  
-- WITH QUERY EXPANSION

Sample Dataset

This tutorial uses a 100,000-row sample table:

1
2
3
4
5
6
7
8
9
CREATE TABLE `fx` (
  `id` int NOT NULL AUTO_INCREMENT,
  `s1` varchar(200) DEFAULT NULL,
  `log_time` datetime DEFAULT NULL,
  `s2` varchar(200) DEFAULT NULL,
  `s3` text,
  PRIMARY KEY (`id`),
  KEY `idx_log_time` (`log_time`)
);

Natural Language Mode

The default mode for full-text searches:

SQL 1 - Basic Usage

1
2
3
SELECT COUNT(*) 
FROM fx 
WHERE MATCH(s1) AGAINST ('cluster' IN NATURAL LANGUAGE MODE);

SQL 2 - Result Filtering

1
2
3
SELECT COUNT(*) 
FROM fx 
WHERE MATCH(s1) AGAINST ('cluster' IN NATURAL LANGUAGE MODE) > 0;

SQL 3 - Performance Comparison

1
2
3
-- Full Table Scan (Avoid This Pattern!)
SELECT COUNT(IF(MATCH(s1) AGAINST ('cluster'), 1, NULL)) AS "count(*)" 
FROM fx;

Execution Plan Analysis:

Query	Execution Plan Key	Performance
SQL 1	`Select tables optimized away`	Optimal
SQL 2	`Using where; Ft_hints: rank > 0`	Efficient
SQL 3	`Type: ALL (Full Table Scan)`	Poor

Relevance Scoring

Full-text indexes calculate relevance scores to rank results:

SQL 4 - Retrieve Scores

1
2
3
4
5
SELECT 
    s1, 
    MATCH(s1) AGAINST ('mysql' IN NATURAL LANGUAGE MODE) AS relevance 
FROM fx 
WHERE MATCH(s1) AGAINST ('mysql' IN NATURAL LANGUAGE MODE) > 0;

SQL 5 - Sorted Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT 
    s1, 
    relevance 
FROM (
    SELECT 
        s1,
        MATCH(s1) AGAINST ('mysql') AS relevance 
    FROM fx 
    WHERE MATCH(s1) AGAINST ('mysql')
) ranked_results 
ORDER BY relevance DESC;

SQL 6 - Combined Search

1
2
3
4
5
-- Search for records containing BOTH 'mysql' AND 'oracle'
SELECT s1 
FROM fx 
WHERE MATCH(s1) AGAINST ('mysql' IN NATURAL LANGUAGE MODE)
  AND MATCH(s1) AGAINST ('oracle' IN NATURAL LANGUAGE MODE);

Boolean Mode

Advanced search with boolean operators:

SQL 7 - OR Logic

1
2
3
4
-- Include EITHER `mysql` OR `oracle`
SELECT s1 
FROM fx 
WHERE MATCH(s1) AGAINST ('mysql oracle' IN BOOLEAN MODE);

SQL 8 - AND/OR Logic

1
2
3
4
5
6
7
8
9
-- Include BOTH `mysql` AND `oracle`
SELECT s1 
FROM fx 
WHERE MATCH(s1) AGAINST ('+mysql +oracle' IN BOOLEAN MODE);

-- Include `mysql` OR `oracle`
SELECT s1 
FROM fx 
WHERE MATCH(s1) AGAINST ('mysql oracle' IN BOOLEAN MODE);

SQL 9 - Exclusions & Boosting

1
2
3
4
5
6
7
-- Include `mysql/oracle` but EXCLUDE `postgresql/sqlserver/mongodb`
SELECT s1 
FROM fx 
WHERE MATCH(s1) AGAINST (
    '+mysql +oracle -postgresql -mongodb -sqlserver' 
    IN BOOLEAN MODE
);

Query Expansion Mode

Expand search results using semantic associations:

SQL 10 - OR Logic

1
2
3
4
-- Search `database` and INCLUDE related terms like `mysql/oracle`
SELECT * 
FROM fx_few 
WHERE MATCH(s1) AGAINST ('database' WITH QUERY EXPANSION);

SQL 11 - Contextual Expansion

1
2
3
4
-- Search `database` and INCLUDE related terms like `mysql/oracle`
SELECT * 
FROM fx_few 
WHERE MATCH(s1) AGAINST ('database' WITH QUERY EXPANSION);

Key Differences

Feature	Natural Language	Boolean Mode	Query Expansion
Syntax	`IN NATURAL...`	`+ - > <`	`WITH EXPANSION`
Ranking	Automatic	Manual	Hybrid
Performance	Best	Good	Moderate
Use Case	Simple Search	Complex Filters	Fuzzy Search

👋 See you in the next lesson.

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.