MySQL Full-Text Indexes

Introduction

When working with databases that contain large amounts of text data, standard comparison operators like LIKE or regular expressions can become inefficient. MySQL's full-text indexing provides a powerful solution for performing fast text searches across your text columns. Full-text indexes enable natural language search capabilities, allowing you to find records based on relevance rankings rather than exact matches.

In this tutorial, you'll learn:

• What full-text indexes are and how they differ from regular indexes
• How to create and configure full-text indexes
• Different search modes available with full-text search
• Practical examples demonstrating full-text search functionality
• Best practices and performance considerations

What Are Full-Text Indexes?

Full-text indexes are specialized indexes designed specifically for text search operations. Unlike regular B-tree indexes that excel at finding exact matches or ranges, full-text indexes are optimized for searching through large amounts of textual content.

Key Features of Full-Text Indexes:

• Word-based indexing: Indexes individual words rather than the entire column
• Relevance ranking: Returns results ordered by relevance to the search query
• Natural language processing: Understands word variations and can ignore common words
• Boolean mode: Supports complex queries with operators like AND, OR, and NOT
• Phrase searching: Can match exact phrases in addition to individual words

When to Use Full-Text Indexes:

Full-text indexes are ideal when:

1. You need to search through large text fields (like articles, descriptions, or comments)
2. You want to rank results by relevance
3. Your queries include natural language patterns
4. Standard LIKE queries are becoming performance bottlenecks

Creating Full-Text Indexes

You can create full-text indexes at table creation time or add them to existing tables.

Creating a Full-Text Index on a New Table

sql

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    author VARCHAR(100),
    published_date DATE,
    FULLTEXT INDEX ft_content (content),
    FULLTEXT INDEX ft_title_content (title, content)
);

In this example, we created:

• A single-column full-text index on the content column
• A multi-column full-text index on both title and content columns

Adding a Full-Text Index to an Existing Table

sql

ALTER TABLE articles
ADD FULLTEXT INDEX ft_content (content);

Or using the CREATE INDEX syntax:

sql

CREATE FULLTEXT INDEX ft_title_content ON articles (title, content);

Full-Text Index Storage Engines

Full-text indexes are supported by:

• InnoDB (from MySQL 5.6 and later)
• MyISAM

For optimal full-text search performance, ensure your MySQL version supports full-text indexing for your chosen storage engine.

Using Full-Text Search

MySQL provides three different modes for full-text searching:

1. Natural Language Mode - The default mode that searches for words and ranks results
2. Boolean Mode - Allows special operators for more complex searches
3. Query Expansion - Enhances searches based on the most relevant results from an initial search

Natural Language Mode

This is the default mode for full-text searches.

sql

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

This query will:

1. Search for "database" and "optimization" in the title and content columns
2. Order results by relevance score (highest first)
3. Filter out rows with low relevance scores

You can also get the relevance score:

sql

SELECT *, MATCH(title, content) AGAINST('database optimization') AS score
FROM articles
WHERE MATCH(title, content) AGAINST('database optimization')
ORDER BY score DESC;

Example Output:

+----+-------------------------+-------------------------------------------+----------+---------------+--------+
| id | title                   | content                                   | author   | published_date| score  |
+----+-------------------------+-------------------------------------------+----------+---------------+--------+
| 5  | MySQL Database Optimization| Tips for optimizing your MySQL database ...| John Doe | 2023-01-15   | 2.7549 |
| 12 | Optimizing SQL Queries  | Database optimization begins with proper...| Jane Smith| 2023-03-22   | 1.3856 |
| 8  | Database Design Patterns| When designing a database, optimization...| Alex Wong| 2022-11-10   | 0.9235 |
+----+-------------------------+-------------------------------------------+----------+---------------+--------+

Boolean Mode

Boolean mode gives you more control over your search with special operators.

sql

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

This search finds articles:

• That MUST contain "database" (+database)
• That MUST NOT contain "cloud" (-cloud)
• That MUST contain "optimization" (+optimization)

Boolean Mode Operators

Operator	Description	Example
+	The word must be present	+mysql
-	The word must not be present	-oracle
*	Wildcard (suffix only)	data* matches database, datatype
"	Phrase search	"database server"
()	Grouping	(mysql postgresql) +performance
>	Increases word contribution to relevance	>mysql optimization
<	Decreases word contribution to relevance	<theory optimization

Boolean Mode Example:

sql

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('"mysql performance" +optimization -theory' IN BOOLEAN MODE);

This finds articles containing the exact phrase "mysql performance" and the word "optimization" but excluding articles containing "theory".

Query Expansion (With Expansion)

Query expansion performs an initial search and then does a second pass using the most relevant terms from the first search.

sql

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

This is useful when:

• Users provide short queries
• You want to find related content beyond exact keyword matches
• You need more comprehensive results for ambiguous searches

Practical Example: Building a Simple Search Engine

Let's implement a simple article search feature using full-text indexes.

1. First, create and populate a table:

sql

-- Create articles table with full-text indexes
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    category VARCHAR(100),
    published_date DATE,
    FULLTEXT INDEX ft_title_content (title, content)
);

-- Insert sample data
INSERT INTO articles (title, content, category, published_date)
VALUES
('MySQL Performance Tuning', 'Learn how to optimize MySQL database performance through proper indexing and query optimization techniques.', 'Database', '2023-01-15'),
('Getting Started with NoSQL', 'NoSQL databases like MongoDB provide scalable alternatives to traditional relational databases.', 'Database', '2023-02-20'),
('SQL Query Optimization', 'Optimize your SQL queries by understanding execution plans and index usage in database systems.', 'Database', '2023-03-10'),
('Web Development with Node.js', 'Build scalable web applications using Node.js and modern JavaScript frameworks.', 'Web Development', '2023-04-05'),
('Database Indexing Strategies', 'Proper indexing is crucial for database performance. Learn various indexing techniques including B-tree and hash indexes.', 'Database', '2023-05-12');

2. Create a PHP function for article search:

php

<?php
function searchArticles($searchTerm, $mode = 'NATURAL') {
    $conn = new mysqli('localhost', 'username', 'password', 'database');
    
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    
    // Escape search term
    $searchTerm = $conn->real_escape_string($searchTerm);
    
    // Define search mode
    $searchMode = $mode === 'BOOLEAN' ? 'IN BOOLEAN MODE' : '';
    
    // Perform search
    $sql = "SELECT *, MATCH(title, content) AGAINST('$searchTerm' $searchMode) AS relevance 
            FROM articles 
            WHERE MATCH(title, content) AGAINST('$searchTerm' $searchMode)
            ORDER BY relevance DESC";
    
    $result = $conn->query($sql);
    $articles = [];
    
    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            $articles[] = $row;
        }
    }
    
    $conn->close();
    return $articles;
}

// Example usage:
$results = searchArticles('database performance');
$booleanResults = searchArticles('+database -nosql', 'BOOLEAN');
?>

3. Create a simple search interface:

html

<!DOCTYPE html>
<html>
<head>
    <title>Article Search</title>
    <style>
        .result { margin-bottom: 20px; padding: 10px; border: 1px solid #ddd; }
        .relevance { color: #888; font-size: 0.8em; }
    </style>
</head>
<body>
    <h1>Article Search</h1>
    
    <form method="GET">
        <input type="text" name="q" placeholder="Search articles..." value="<?php echo htmlspecialchars($_GET['q'] ?? ''); ?>">
        <select name="mode">
            <option value="NATURAL" <?php echo ($_GET['mode'] ?? '') === 'NATURAL' ? 'selected' : ''; ?>>Natural Language</option>
            <option value="BOOLEAN" <?php echo ($_GET['mode'] ?? '') === 'BOOLEAN' ? 'selected' : ''; ?>>Boolean Mode</option>
        </select>
        <button type="submit">Search</button>
    </form>
    
    <div id="results">
        <?php
        if (isset($_GET['q']) && !empty($_GET['q'])) {
            $results = searchArticles($_GET['q'], $_GET['mode'] ?? 'NATURAL');
            
            if (empty($results)) {
                echo "<p>No results found.</p>";
            } else {
                foreach ($results as $article) {
                    echo "<div class='result'>";
                    echo "<h3>" . htmlspecialchars($article['title']) . "</h3>";
                    echo "<p>" . htmlspecialchars($article['content']) . "</p>";
                    echo "<div class='relevance'>Relevance score: " . number_format($article['relevance'], 4) . "</div>";
                    echo "<div>Category: " . htmlspecialchars($article['category']) . " | Published: " . $article['published_date'] . "</div>";
                    echo "</div>";
                }
            }
        }
        ?>
    </div>
</body>
</html>

Performance Considerations

To get the most out of full-text indexes:

1. Index size: Full-text indexes can be large. Only include columns that need to be searched.

2. Stopwords: MySQL ignores common words (like "the", "and", "is") by default. You can customize the stopword list:

sql

-- View current stopword list
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

-- Create custom stopword table
CREATE TABLE my_stopwords (value VARCHAR(30)) ENGINE = INNODB;
INSERT INTO my_stopwords (value) VALUES ('example'), ('custom'), ('stopword');

-- Set custom stopword table (in my.cnf or global variables)
SET GLOBAL innodb_ft_server_stopword_table = 'database_name/my_stopwords';

3. Minimum word length: By default, MySQL ignores words shorter than 3 characters. Modify this with:

sql

-- In my.cnf configuration file
ft_min_word_len=2  # For MyISAM
innodb_ft_min_token_size=2  # For InnoDB

After changing these settings, you must rebuild your full-text indexes:

sql

REPAIR TABLE articles QUICK;  # For MyISAM
ALTER TABLE articles DROP INDEX ft_title_content;
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_content (title, content);

4. Stemming: MySQL doesn't provide built-in stemming (reducing words to their root form), but you can implement partial solutions using the wildcard operator in boolean mode (optimiz* to match "optimize", "optimizing", etc.).

Full-Text Index Limitations

• Cannot use full-text indexes for ORDER BY or GROUP BY
• Cannot combine full-text searches with other conditions using indexes
• Full-text search does not support regex patterns
• InnoDB full-text indexes have different behaviors than MyISAM
• Limited language support for word boundary detection

Summary

MySQL full-text indexes provide a powerful way to implement text search functionality in your applications:

1. Fast text searching: Much more efficient than LIKE queries for large text data
2. Flexible search modes: Natural language, boolean, and query expansion options
3. Relevance ranking: Results ordered by importance to the search query
4. Multiple column indexing: Can search across several text fields simultaneously

By implementing full-text indexes properly, you can dramatically improve the search experience in your applications and efficiently handle text-based queries even as your data grows.

Additional Resources and Exercises

Resources

• MySQL Documentation on Full-Text Search
• MySQL Full-Text Stopwords

Exercises

1. Basic Full-Text Search: Create a table with articles containing titles and content. Implement a full-text search to find articles about specific topics.

2. Boolean Mode Practice: Experiment with different boolean operators to refine search results. Try combining +, -, and phrase searches.

3. Relevance Ranking: Create a query that displays search results with their relevance scores, and experiment with how different combinations of words affect the ranking.

4. Search Optimization: Compare the performance of full-text search against LIKE queries using EXPLAIN to analyze the query plans.

5. Advanced Application: Build a product search feature for an e-commerce database that uses full-text search across product names, descriptions, and categories.