Log in
Free Trial
  • main
    /
  • blog
    /
  • Comprehensive Guide to MySQL Full-Text Indexing: Examples, Creation, and Performance Optimization

Comprehensive Guide to MySQL Full-Text Indexing

Examples, Creation, and Performance Optimization
MAY 15, 2025 • WRITTEN BY ROMAN AGABEKOV
Text search in MySQL doesn't have to be inefficient. MySQL's full-text indexing transforms how your applications find and handle large volumes of unstructured text. Instead of the glacial pace of LIKE queries scanning every character, you get a search system that understands words, relevance, and context.

When properly implemented, your users will notice the difference immediately. Results will appear in milliseconds, match their actual intent, and surface the most relevant content first. This guide walks you through everything from setup to optimization, helping you implement search that works as intuitively as your users expect.

A Quick Lesson on MySQL Full-Text Indexing

Full-text indexes are made to search text-heavy fields like TEXT, VARCHAR, and CHAR. Traditional indexes focus on exact matches or numeric ranges, which fall flat when users are looking for natural language results.

When you need to search through actual text content rather than just matching predefined values, full-text indexing is ideal. Common use cases include:

  • Blog platforms and CMS
  • Product catalogs with search features
  • Social media applications or forums with user-generated content
  • Email or message archives
The difference between traditional and full-text approaches is stark. Let’s say you have a database of articles where you want to find all entries containing "performance optimization":

Using LIKE (slow on large datasets):
SELECT * FROM documents WHERE text LIKE '%performance optimization%';
Using full-text search (much faster):
SELECT * FROM documents WHERE MATCH(text) AGAINST('performance optimization');
The LIKE query must scan each and every row and examine the entire text column. The second query leverages a full-text index for dramatically faster results.

How Full-Text Indexing Works in MySQL

To maximize the benefits of comprehensive text searching, you need to understand its inner workings, like how MySQL processes and stores text data, to how different search modes affect query results.

Tokenization and Stopwords

When MySQL creates a full-text index, it performs the following steps:

  • Breaks text values into individual terms (tokens).
  • Excludes common, non-informative words (stopwords).
  • Maps each token to the rows containing it, forming an inverted index.
Stopwords are ignored because they appear in almost every text record and provide little search value. MySQL has a default stopword list, but you can customize it by creating your own list or disabling it entirely:
SET GLOBAL innodb_ft_server_stopword_table = 'database/custom_stopwords';
SET GLOBAL innodb_ft_enable_stopword = OFF;

Search Modes

Now, let’s examine the orders table with SHOW INDEX FROM orders;:
MySQL supports three distinct search methods:

  • Natural Language (NL) Mode (default): Performs ranking-based matching, returning rows that contain one or more of the keywords. For example, searching "database performance" returns documents containing either word, with documents containing both terms ranked higher.

  • Boolean Mode: Allows precise control with operators like + (must contain), - (must not contain), and * (wildcard) for precise searches. Boolean operators include:

+ Word must be present
- Word must not be present
* Wildcard (partial word matching)
"..." Exact phrase
(...) Grouping
> Increases word contribution to relevance
< Decreases contribution to relevance

  • Query Expansion: Executes the original query, finds the most relevant results, then re-executes an expanded version based on related keywords extracted from those top results. It's particularly valuable when searching limited content where users might not know the exact terminology

Relevance Ranking

When you use NL mode, MySQL calculates a relevance score for each match using:

  • Term frequency: How often the term appears in the document
  • Inverse document frequency: How unique the term is across all documents
  • Document length: Normalizing scores based on text length

Rows with higher calculated relevance appear earlier in the result set.

Creating Full-Text Indexes

Adding these indexes is straightforward once you know the basic requirements. Here's how to build this critical search infrastructure:

Prerequisites

  • If you're using InnoDB (the default and recommended storage engine), you'll need MySQL 5.6 or newer. Earlier versions restrict full-text search to the older MyISAM engine only, which lacks transaction support and other modern features.

  • Only works with textual data columns. Numerical columns, date fields, and binary data cannot participate, since they lack natural language content.

  • Table must use either InnoDB or MyISAM storage engine. InnoDB offers better crash recovery and transaction support, while MyISAM might perform marginally better for read-heavy search workloads on older MySQL versions.

Creating a New Table with Full-Text Index

When designing a new database schema with search in mind, you can incorporate full-text indexes directly in your table creation script:
CREATE TABLE cooking_recipes (
  recipe_id INT PRIMARY KEY AUTO_INCREMENT,
  chef_name VARCHAR(100),
  recipe_name VARCHAR(200),
  ingredients TEXT,
  preparation_steps TEXT,
  cuisine_type VARCHAR(50),
  difficulty_level ENUM('Beginner', 'Intermediate', 'Advanced', 'Professional'),
  cooking_time INT COMMENT 'In minutes',
  calories_per_serving INT,
  
  -- Full-text indices for different search use cases
  FULLTEXT INDEX ft_recipe_search (recipe_name, ingredients),
  FULLTEXT INDEX ft_preparation (preparation_steps),
  FULLTEXT INDEX ft_chef_cuisine (chef_name, cuisine_type)
) ENGINE=InnoDB;
This example creates two distinct search capabilities: one index dedicated solely to content and a combined index spanning both chef_name and cuisine_type fields.

Adding to Existing Tables

For established databases where tables already contain valuable text data, you can add this functionality without table recreation:
ALTER TABLE knowledge_base
ADD FULLTEXT INDEX ft_main_content (main_content);
Or you could use:
CREATE FULLTEXT INDEX ft_main_content ON knowledge_base (main_content);
When retrofitting search onto existing tables, pay attention to your timing. Indexing large text columns on tables with millions of rows can lock tables or consume significant system resources during creation. The larger your dataset, the longer the initial indexing process will take, as MySQL must process and tokenize every word in every text field being indexed. Consider scheduling these operations during maintenance windows.

Index Creation Best Practices

Thoughtful index design can dramatically impact both search performance and server resource utilization:
  • Only include columns that users actually search against.
  • Consider content characteristics. Extremely short fields (like two-letter codes) or extremely long fields (like book chapters) may not benefit from full-text indexing.
  • Evaluate information density. Columns containing mostly standardized or repetitive text provide less search value than those with varied, information-rich content.
  • Exclude columns with sensitive data when possible, as indexed content may appear in query cache and other memory structures.

When creating indexes across multiple text columns:
  • Don’t lump unrelated columns into a single massive index. Instead, create targeted indexes for distinct use cases.
  • Place frequently searched columns first in multi-column indexes when those columns often appear alone in queries.
  • Consider column cardinality. Combining a high-cardinality field (like product descriptions) with a low-cardinality field (like categories) may dilute relevance scoring.

Adding a relevance threshold using a condition like (AND MATCH(title, content) AGAINST('term') > 0.5) to eliminate low-quality matches.

Practical Search Examples

Let's walk through real-world search scenarios using a blog database to demonstrate how different MySQL search approaches solve specific problems:

Basic Natural Language Queries

When visitors need to locate articles mentioning certain topics, NL mode provides intuitive results.
SELECT entry_id, subject_line, 
       MATCH(subject_line, main_content) AGAINST('mysql optimization') AS relevance_score
FROM knowledge_base
WHERE MATCH(subject_line, main_content) AGAINST('mysql optimization')
ORDER BY relevance_score DESC
LIMIT 15;
This query returns articles containing either "mysql" or "performance" (or ideally both), with the most relevant articles first. The relevance score helps rank articles where both terms appear prominently higher than those with just passing mentions.

Boolean Mode for Advanced Searches

When you need exact control over search logic, boolean mode lets you create complex conditions.

Finding articles with required and forbidden terms:
SELECT entry_id, subject_line 
FROM knowledge_base
WHERE MATCH(subject_line, main_content) 
      AGAINST('+mysql +optimization -cloud' IN BOOLEAN MODE)
LIMIT 20;
This identifies only articles with both "mysql" AND "optimization" but excludes any mentioning "cloud".

Targeting search to specific fields:
SELECT entry_id, subject_line 
FROM knowledge_base
WHERE MATCH(subject_line) 
      AGAINST('+database' IN BOOLEAN MODE)
LIMIT 25;
This syntax restricts the search to the subject_line for "database" which can be useful when field context matters.

Partial word matching:
SELECT entry_id, subject_line 
FROM knowledge_base
WHERE MATCH(subject_line, main_content) 
      AGAINST('optim*' IN BOOLEAN MODE)
LIMIT 30;
This searches for articles that contain words starting with “optim”, which catches "optimize," "optimization," "optimizing," and other variations. It’s ideal when you're unsure of exact word forms.

Phrase Searching

When word order and proximity matter, use exact phrase searches:
SELECT entry_id, subject_line 
FROM knowledge_base
WHERE MATCH(subject_line, main_content) 
      AGAINST('"mysql performance enhancement"' IN BOOLEAN MODE)
LIMIT 10;

Query Expansion Example

When users might not know the exact terminology or you want to broaden search results:
SELECT entry_id, subject_line 
FROM knowledge_base
WHERE MATCH(subject_line, main_content) 
      AGAINST('database indexing' WITH QUERY EXPANSION)
LIMIT 25;
This first finds articles about "database indexing," then analyzes top results to identify related terms, and finally performs a broader search including those terms. It’s a great method for helping users discover relevant content they might have missed with narrow search terms.

Troubleshooting 3 Common Issues

1. No Results Returned

If your search returns no results when you expect matches:

Check minimum term length: MySQL disregards words shorter than innodb_ft_min_token_size (default: 3 characters). For applications needing to look through these shorter terms, adjust this setting in my.cnf:
innodb_ft_min_token_size=2
After changing token size and restarting MySQL, existing indexes need to be rebuilt:
ALTER TABLE articles DROP INDEX ft_content;
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);
Verify stopwords: Common words like "and" or "the" are removed from the search process automatically. Review the current stopword list to see if your search terms are being filtered out:
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
Confirm index creation: Verify that your indexes were properly created and remain intact, as indexes can sometimes be inadvertently dropped during schema changes.
SHOW INDEX FROM knowledge_base WHERE Key_name LIKE 'ft%';

2. Unexpected Results

When search returns puzzling or seemingly random matches:

Check boolean mode syntax: A single misplaced operator in boolean mode (like forgetting a plus sign) can dramatically alter search behavior, turning required terms into optional ones or vice versa.

Understand tokenization behavior: MySQL breaks text at word boundaries defined by spaces and punctuation, which may not match your expectations for terms like "C++" or "O'Reilly" that contain punctuation.

Test with AGAINST(): Run a query that exposes the internal scoring to see why certain results are included or ranked unexpectedly:
SELECT entry_id, subject_line, 
       MATCH(main_content) AGAINST('sample query') AS ranking_value
FROM knowledge_base
ORDER BY ranking_value DESC
LIMIT 20;

3. Sluggish Execution Times

For performance issues:

Verify server resources - Document indexing operations are memory and CPU intensive. Check if your server is hitting resource limits during peak search activity.

Monitor index size - Oversized indexes can strain memory and slow performance. Measure your index footprint to identify bloated indexes:
SELECT table_name, index_name, stat_value*@@innodb_page_size 
FROM mysql.innodb_index_stats 
WHERE stat_name='size' AND index_name LIKE 'ft%';

Take Your MySQL Search Skills to the Next Level

MySQL full-text indexing requires some understanding to implement properly, but the benefits for text-heavy applications are substantial. If you implement the techniques covered in this guide, you'll deliver faster, more intuitive search experiences while reducing server load.

For applications with more advanced requirements, you might eventually outgrow MySQL's built-in capabilities and need a dedicated search solution like Elasticsearch. However, for many applications, MySQL's full-text indexing provides an excellent balance of convenience and performance without adding external dependencies.

We encourage you to dive deeper into this topic. These resources will help you broaden your understanding, so you can build more powerful search features for your applications:

WRITTEN BY ROMAN AGABEKOV
Keeping an Eye on MySQL with Health Checks
MySQL Performance Tuning Guide
How MySQL Configuration Impacts the Performance of Web Applications
How do you keep high-performance MySQL configuration?
Explore More
Ready to dive in?
Try Releem today for FREE!
Start Free Trial