MySQL Full-Text Search Optimization

MySQL Full-Text Search Optimization

Full-text search enables searching natural language text efficiently, going beyond simple LIKE queries. MySQL's full-text search features allow you to build powerful search functionality directly in your database, eliminating the need for external search engines in many use cases.

🎯 Understanding Full-Text Search

What is Full-Text Search?

Full-text search indexes words within text columns and allows sophisticated queries against those words, including relevance ranking, stemming, and boolean operators.

💡 Key Insight: Full-text search is ideal for searching user-generated content, product descriptions, articles, and any natural language text where relevance matters.

🔧 Creating Full-Text Indexes

Basic Syntax

-- Create table with full-text index
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    author VARCHAR(100),
    published_at DATETIME,
    FULLTEXT INDEX ft_title (title),
    FULLTEXT INDEX ft_content (content),
    FULLTEXT INDEX ft_title_content (title, content)
) ENGINE=InnoDB;

-- Add full-text index to existing table
ALTER TABLE articles
    ADD FULLTEXT INDEX ft_search (title, content);

-- Or using CREATE INDEX
CREATE FULLTEXT INDEX ft_search ON articles(title, content);

InnoDB vs MyISAM Full-Text Search

-- InnoDB (Recommended for MySQL 5.6+)
CREATE TABLE products_innodb (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    FULLTEXT INDEX ft_search (name, description)
) ENGINE=InnoDB;

-- MyISAM (Legacy, still useful for read-only search tables)
CREATE TABLE products_myisam (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    FULLTEXT INDEX ft_search (name, description)
) ENGINE=MyISAM;

📦 Search Modes

Natural Language Mode

Natural language mode ranks results by relevance automatically.

-- Basic natural language search
SELECT
    id,
    title,
    MATCH(title, content) AGAINST('database optimization') AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('database optimization')
ORDER BY relevance DESC;

-- Explicit natural language mode
SELECT *
FROM articles
WHERE MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);

-- Search with minimum relevance threshold
SELECT
    id,
    title,
    MATCH(title, content) AGAINST('database optimization') AS score
FROM articles
WHERE MATCH(title, content) AGAINST('database optimization') > 0.5
ORDER BY score DESC
LIMIT 20;

Boolean Mode

Boolean mode provides precise control over search terms.

-- Basic boolean search
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+mysql +optimization' IN BOOLEAN MODE);

-- Boolean operators
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST(
    '+database +performance -oracle'  -- Must have database AND performance, NOT oracle
    IN BOOLEAN MODE
);

-- Phrase search with quotes
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST(
    '"query optimization"'  -- Exact phrase
    IN BOOLEAN MODE
);

-- Wildcard search
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST(
    'optim*'  -- Matches optimization, optimizing, optimizer, etc.
    IN BOOLEAN MODE
);

-- Weighted terms
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST(
    '+mysql >performance <tutorial'  -- mysql required, performance more important, tutorial less
    IN BOOLEAN MODE
);

Boolean Operators Reference

Query Expansion Mode

Query expansion automatically adds related terms to broaden the search.

-- Query expansion (useful for short searches)
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST(
    'database'
    WITH QUERY EXPANSION
);

-- This internally runs two queries:
-- 1. First query finds documents matching 'database'
-- 2. Second query uses top results to find related terms
-- 3. Returns documents matching original + related terms

🚀 Performance Optimization

Configuration Parameters

# /etc/mysql/mysql.conf.d/fulltext.cnf

[mysqld]
# InnoDB full-text settings
innodb_ft_min_token_size = 3      # Minimum word length (default: 3)
innodb_ft_max_token_size = 84     # Maximum word length (default: 84)
innodb_ft_enable_stopword = ON    # Enable stopword filtering
innodb_ft_server_stopword_table = 'mydb/custom_stopwords'
innodb_ft_cache_size = 8000000    # Cache size for full-text index (default: 8MB)
innodb_ft_result_cache_limit = 2000000000  # Result cache limit (2GB)
innodb_ft_sort_pll_degree = 2     # Parallel sorting threads

# MyISAM full-text settings (if using MyISAM)
ft_min_word_len = 3               # Minimum word length (default: 4)
ft_max_word_len = 84              # Maximum word length
ft_stopword_file = '/etc/mysql/stopwords.txt'

Custom Stopwords

-- Create custom stopwords table
CREATE TABLE custom_stopwords (
    value VARCHAR(30) NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

-- Insert custom stopwords
INSERT INTO custom_stopwords (value) VALUES
    ('the'), ('and'), ('or'), ('but'), ('this'), ('that'),
    ('with'), ('from'), ('your'), ('have'), ('has'),
    ('just'), ('about'), ('also'), ('very'), ('really');

-- Configure MySQL to use custom stopwords
SET GLOBAL innodb_ft_server_stopword_table = 'your_database/custom_stopwords';

-- Rebuild full-text indexes after changing stopwords
ALTER TABLE articles DROP INDEX ft_search;
ALTER TABLE articles ADD FULLTEXT INDEX ft_search (title, content);

Index Optimization

-- Optimize full-text index (InnoDB)
OPTIMIZE TABLE articles;

-- Force index rebuild
ALTER TABLE articles ENGINE=InnoDB;

-- Check index status
SELECT
    TABLE_NAME,
    INDEX_NAME,
    INDEX_TYPE,
    STAT_VALUE
FROM mysql.innodb_index_stats
WHERE stat_name = 'n_leaf_pages'
    AND index_name LIKE 'ft_%';

-- Monitor full-text index size
SELECT
    TABLE_NAME,
    ROUND(SUM(stat_value) * @@innodb_page_size / 1024 / 1024, 2) AS 'Size (MB)'
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
    AND index_name LIKE 'ft_%'
GROUP BY TABLE_NAME;

📊 Advanced Search Patterns

Weighted Multi-Column Search

-- Weight title matches higher than content
SELECT
    id,
    title,
    (MATCH(title) AGAINST('mysql optimization') * 2.0) +
    (MATCH(content) AGAINST('mysql optimization') * 1.0) AS weighted_score
FROM articles
WHERE MATCH(title, content) AGAINST('mysql optimization')
ORDER BY weighted_score DESC
LIMIT 20;

-- Alternative using boolean mode for control
SELECT
    id,
    title,
    MATCH(title) AGAINST('mysql optimization' IN BOOLEAN MODE) * 3 +
    MATCH(content) AGAINST('mysql optimization' IN BOOLEAN MODE) AS score
FROM articles
HAVING score > 0
ORDER BY score DESC;

Category-Filtered Search

-- Combine full-text with filters
SELECT
    a.id,
    a.title,
    MATCH(a.title, a.content) AGAINST('database performance') AS relevance
FROM articles a
JOIN categories c ON a.category_id = c.id
WHERE MATCH(a.title, a.content) AGAINST('database performance')
    AND c.slug = 'technology'
    AND a.published_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
ORDER BY relevance DESC
LIMIT 20;

Search with Highlighting

-- Extract matching context (pseudo-highlighting)
DELIMITER //

CREATE FUNCTION highlight_match(
    content TEXT,
    search_term VARCHAR(255),
    context_length INT
)
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE pos INT;
    DECLARE snippet TEXT;

    SET pos = LOCATE(search_term, LOWER(content));

    IF pos = 0 THEN
        RETURN LEFT(content, context_length * 2);
    END IF;

    SET snippet = SUBSTRING(
        content,
        GREATEST(1, pos - context_length),
        context_length * 2 + LENGTH(search_term)
    );

    RETURN CONCAT('...', snippet, '...');
END //

DELIMITER ;

-- Use in query
SELECT
    id,
    title,
    highlight_match(content, 'optimization', 50) AS snippet,
    MATCH(title, content) AGAINST('optimization') AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('optimization')
ORDER BY relevance DESC;

Faceted Search

-- Search with category facets
SELECT
    c.name AS category,
    COUNT(*) AS article_count
FROM articles a
JOIN categories c ON a.category_id = c.id
WHERE MATCH(a.title, a.content) AGAINST('mysql performance')
GROUP BY c.id, c.name
ORDER BY article_count DESC;

-- Search with date facets
SELECT
    YEAR(published_at) AS year,
    MONTH(published_at) AS month,
    COUNT(*) AS count
FROM articles
WHERE MATCH(title, content) AGAINST('database optimization')
GROUP BY YEAR(published_at), MONTH(published_at)
ORDER BY year DESC, month DESC;

🔍 Search Suggestions and Autocomplete

Implementing Autocomplete

-- Create search terms table for autocomplete
CREATE TABLE search_terms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    term VARCHAR(255) NOT NULL,
    search_count INT DEFAULT 1,
    last_searched TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX idx_term (term),
    FULLTEXT INDEX ft_term (term)
) ENGINE=InnoDB;

-- Store search terms (call from application)
INSERT INTO search_terms (term, search_count, last_searched)
VALUES ('mysql optimization', 1, NOW())
ON DUPLICATE KEY UPDATE
    search_count = search_count + 1,
    last_searched = NOW();

-- Autocomplete query
SELECT term, search_count
FROM search_terms
WHERE term LIKE CONCAT(?, '%')  -- Prefix match
   OR MATCH(term) AGAINST(? IN BOOLEAN MODE)  -- Full-text fallback
ORDER BY
    CASE WHEN term LIKE CONCAT(?, '%') THEN 0 ELSE 1 END,
    search_count DESC
LIMIT 10;

"Did You Mean" Suggestions

-- Simple Levenshtein-based suggestions
DELIMITER //

CREATE FUNCTION levenshtein(s1 VARCHAR(255), s2 VARCHAR(255))
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE s1_len, s2_len, i, j, c, c_temp INT;
    DECLARE cv0, cv1 VARBINARY(256);

    SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2);
    IF s1_len = 0 THEN RETURN s2_len; END IF;
    IF s2_len = 0 THEN RETURN s1_len; END IF;

    SET cv1 = 0x00, j = 1;
    WHILE j <= s2_len DO
        SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1;
    END WHILE;

    SET i = 1;
    WHILE i <= s1_len DO
        SET cv0 = UNHEX(HEX(i)), j = 1;
        WHILE j <= s2_len DO
            SET c = ORD(MID(cv1, j, 1)) + 1;
            IF SUBSTRING(s1, i, 1) = SUBSTRING(s2, j, 1) THEN
                SET c_temp = ORD(MID(cv1, j - 1, 1));
            ELSE
                SET c_temp = ORD(MID(cv1, j - 1, 1)) + 1;
            END IF;
            IF c > c_temp THEN SET c = c_temp; END IF;
            SET c_temp = ORD(MID(cv0, j - 1, 1)) + 1;
            IF c > c_temp THEN SET c = c_temp; END IF;
            SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
        END WHILE;
        SET cv1 = cv0, i = i + 1;
    END WHILE;

    RETURN c;
END //

DELIMITER ;

-- Find similar terms
SELECT term
FROM search_terms
WHERE levenshtein(term, 'myql') <= 2  -- Allow 2 character edits
ORDER BY search_count DESC
LIMIT 5;

⚠️ Common Issues and Solutions

Issue 1: No Results for Short Words

-- Check minimum word length
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
SHOW VARIABLES LIKE 'ft_min_word_len';

-- Solution: Reduce minimum word length (requires restart and index rebuild)
-- In my.cnf:
-- innodb_ft_min_token_size = 2

-- Then rebuild index
ALTER TABLE articles DROP INDEX ft_search;
ALTER TABLE articles ADD FULLTEXT INDEX ft_search (title, content);

Issue 2: Common Words Return No Results

-- Problem: Stopwords are filtered
-- Solution: Use custom stopword table or boolean mode with explicit inclusion

-- Check current stopwords
SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

-- Or use boolean mode for exact matching
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('"the matrix"' IN BOOLEAN MODE);

Issue 3: Search Not Returning Expected Results

-- Debug: Check what's in the full-text index
SET GLOBAL innodb_ft_aux_table = 'your_database/articles';

-- View indexed words
SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE
WHERE doc_id = (SELECT FTS_DOC_ID FROM articles WHERE id = 123);

-- View deleted documents pending cleanup
SELECT * FROM information_schema.INNODB_FT_DELETED;

-- Force optimization to clean up
OPTIMIZE TABLE articles;

📈 Monitoring and Tuning

Performance Metrics

-- Full-text search statistics
SHOW STATUS LIKE 'Innodb_ft%';

-- Index size
SELECT
    TABLE_NAME,
    INDEX_NAME,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
    AND INDEX_NAME LIKE 'FTS_%';

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

Benchmark Comparison

✅ Best Practices Checklist

Index Design

• [ ] Create composite full-text indexes for multi-column searches
• [ ] Use appropriate minimum word length for your content
• [ ] Configure custom stopwords for your domain
• [ ] Regularly optimize full-text indexes

Query Design

• [ ] Use boolean mode for precise control
• [ ] Implement relevance thresholds
• [ ] Weight columns appropriately
• [ ] Combine with regular indexes for filters

Maintenance

• [ ] Monitor index sizes
• [ ] Run OPTIMIZE TABLE periodically
• [ ] Track slow full-text queries
• [ ] Update stopwords as needed

🎓 Conclusion

MySQL full-text search is a powerful feature that can handle many search requirements without external search engines. By understanding the different search modes, optimizing your indexes, and following best practices, you can build efficient and relevant search functionality.

💡 Pro Tip: For very large datasets (100M+ rows) or complex search requirements (faceting, synonyms, fuzzy matching), consider complementing MySQL full-text with dedicated search engines like Elasticsearch or Apache Solr.

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in MySQL, SQL optimization, and enterprise database solutions.

Related Articles

• MySQL Index Optimization Strategies
• MySQL Query Optimization Expert Tips
• MySQL Performance Schema Monitoring

Need help with MySQL full-text search? Contact us for expert database consulting.