MySQL Full-Text Search

MySQL Full-Text Search (FTS) is a powerful way to perform advanced text-based searches within database tables. Unlike simple pattern matching with LIKE, Full-Text Search allows natural language queries, boolean searches, and query expansion, making it ideal for searching large text-based datasets efficiently.

1. What is MySQL Full-Text Search?

Full-Text Search (FTS) enables searching for complex text patterns in MySQL databases, providing fast and accurate results compared to regular SQL searches.

Key Features:

✅ Supports Natural Language searches
✅ Allows Boolean Operators (AND, OR, NOT, etc.)
✅ Works with large text fields (TEXT, VARCHAR)
✅ Uses indexes for better performance

2. Enabling Full-Text Search in MySQL

Supported Storage Engines

InnoDB (MySQL 5.6+) ✅
MyISAM ✅

Supported Data Types

CHAR
VARCHAR
TEXT (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT)

3. Creating a Full-Text Index

To enable FTS, you must create a FULLTEXT index on the desired columns.

Example: Creating a Table with Full-Text Index

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT(title, content)  -- Creating Full-Text Index
);

If the table already exists, add an index using:

ALTER TABLE articles ADD FULLTEXT(title, content);

4. Performing Full-Text Searches

4.1 Natural Language Search (Default Mode)

MySQL automatically ranks results based on relevance.

Example: Searching for "database optimization"

SELECT * FROM articles 
WHERE MATCH(title, content) 
AGAINST('database optimization');

Returns rows ordered by relevance (best match first).
Stopwords (common words like "the", "and") are ignored.
Searches for inflectional forms (e.g., "running" matches "run").

4.2 Boolean Mode Search

Allows fine-grained searches using operators like +, -, *, etc.

Example: Boolean Search

SELECT * FROM articles 
WHERE MATCH(title, content) 
AGAINST('+MySQL -MongoDB' IN BOOLEAN MODE);

✅ +MySQL → Must contain "MySQL"
❌ -MongoDB → Must NOT contain "MongoDB"

Boolean Operators in MySQL Full-Text Search

Operator	Meaning
`+`	Word must be present
`-`	Word must NOT be present
`*`	Wildcard (e.g., `run*` finds "running")
`"`	Exact phrase match (`"database optimization"`)
`>` / `<`	Adjust ranking (e.g., `>MySQL <MongoDB`)

Example: Searching for exact phrases

SELECT * FROM articles 
WHERE MATCH(title, content) 
AGAINST('"database optimization"' IN BOOLEAN MODE);

4.3 Query Expansion Mode

MySQL automatically expands queries based on related words.

Example: Using Query Expansion

SELECT * FROM articles 
WHERE MATCH(title, content) 
AGAINST('search engine' WITH QUERY EXPANSION);

MySQL first searches for "search engine", then adds similar words to refine the results.

5. Ranking & Relevance in Full-Text Search

MySQL assigns a relevance score (higher = better match).

Example: Displaying Relevance Score

SELECT id, title, 
       MATCH(title, content) AGAINST('MySQL Performance') AS relevance 
FROM articles 
ORDER BY relevance DESC;

Higher values indicate better matches.

6. Handling Stopwords & Minimum Word Length

By default, MySQL ignores common words (e.g., "the", "is") and short words (<4 characters).

Modifying Stopwords

To customize stopwords:

Create a custom stopword file (e.g., /var/lib/mysql/stopwords.txt).

Modify MySQL settings:

innodb_ft_server_stopword_table = mydatabase.my_stopwords

Restart MySQL.

Changing Minimum Word Length

By default, words < 4 characters are ignored. To allow shorter words:

[mysqld]
ft_min_word_len=3

Then rebuild the index:

REPAIR TABLE articles QUICK;

7. Full-Text Search vs. LIKE

Feature	FULLTEXT Search	LIKE Operator
Speed	Faster (uses index)	Slow (no index)
Ranking	Yes (relevance score)	No ranking
Wildcards	Supports (`*`, `"..."`)	Only `%` and `_`
Boolean Search	Yes (`+`, `-`, etc.)	No
Large Text Fields	Efficient	Slow

✅ Use FULLTEXT for complex searches on large text fields.
❌ Use LIKE only for simple string matches.

8. Common Issues & Troubleshooting

Issue 1: No Results for Common Words

MySQL ignores stopwords.
Solution: Use Boolean Mode or disable stopwords.

Issue 2: Small Words Not Found

Words shorter than 4 characters are ignored.
Solution: Change ft_min_word_len to 3.

Issue 3: No Results in Boolean Mode

Must use Boolean operators (+, -, *).
Example: AGAINST('+MySQL' IN BOOLEAN MODE).

9. Conclusion

✅ MySQL Full-Text Search is powerful for searching large text datasets.
✅ It supports natural language, boolean search, and query expansion.
✅ Indexes improve performance compared to LIKE.

For advanced text searching, Full-Text Search is a must-have tool in MySQL! 🚀

Souy Soeng

MySQL Full-Text Search