Eric Campbell 4 min read Databases

MySQL Full-Text Search: Natural Language Mode

Natural Language Mode is MySQL's default full-text search mode, designed to process queries the way users naturally express them. Unlike Boolean Mode, it doesn't require special operators—users...

Key Insights

  • Natural Language Mode in MySQL full-text search ranks results by relevance using statistical analysis, making it ideal for search interfaces where users enter queries in plain language
  • MySQL automatically filters out common words (stopwords) and requires a minimum word length of 3-4 characters by default, which can significantly impact search behavior and requires careful configuration
  • Full-text indexes consume additional storage and have specific limitations—they work only on InnoDB and MyISAM tables with CHAR, VARCHAR, or TEXT columns, and rebuilding them on large tables can be resource-intensive

Understanding Natural Language Full-Text Search

Natural Language Mode is MySQL’s default full-text search mode, designed to process queries the way users naturally express them. Unlike Boolean Mode, it doesn’t require special operators—users simply type their search terms, and MySQL returns results ranked by relevance.

The relevance calculation uses a sophisticated algorithm based on term frequency-inverse document frequency (TF-IDF). Words that appear frequently in a specific document but rarely across all documents receive higher weights. This means searching for “kubernetes deployment strategies” will rank documents containing all three terms higher than those with only one or two.

Creating Full-Text Indexes

Before performing full-text searches, you need to create a full-text index. Here’s a practical example using a technical documentation table:

CREATE TABLE documentation (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT INDEX ft_title_content (title, content)
) ENGINE=InnoDB;

You can also add full-text indexes to existing tables:

ALTER TABLE documentation 
ADD FULLTEXT INDEX ft_content (content);

For multi-column searches across title and content:

ALTER TABLE documentation 
ADD FULLTEXT INDEX ft_search (title, content);

Basic Natural Language Queries

The MATCH() AGAINST() syntax performs full-text searches. The MATCH() function specifies which columns to search, while AGAINST() contains the search terms:

-- Search in content column
SELECT id, title, 
       SUBSTRING(content, 1, 200) as excerpt
FROM documentation
WHERE MATCH(content) AGAINST('microservices architecture');

For multi-column searches:

SELECT id, title,
       MATCH(title, content) AGAINST('kubernetes deployment') as relevance
FROM documentation
WHERE MATCH(title, content) AGAINST('kubernetes deployment')
ORDER BY relevance DESC;

The relevance score helps you understand why results appear in a specific order. Higher scores indicate better matches:

SELECT title,
       MATCH(title, content) AGAINST('database optimization') as score
FROM documentation
WHERE MATCH(title, content) AGAINST('database optimization')
HAVING score > 0
ORDER BY score DESC
LIMIT 10;

Relevance Ranking in Action

MySQL’s relevance algorithm considers multiple factors. Here’s how to leverage this for better search results:

-- Get top 5 most relevant articles about "caching strategies"
SELECT 
    title,
    category,
    MATCH(title, content) AGAINST('caching strategies') as relevance_score
FROM documentation
WHERE MATCH(title, content) AGAINST('caching strategies')
ORDER BY relevance_score DESC
LIMIT 5;

Documents containing exact phrases or multiple search terms score higher:

-- Compare relevance for different search terms
SELECT 
    title,
    MATCH(title, content) AGAINST('redis') as redis_score,
    MATCH(title, content) AGAINST('redis cache') as redis_cache_score,
    MATCH(title, content) AGAINST('redis caching strategies') as full_score
FROM documentation
WHERE MATCH(title, content) AGAINST('redis caching strategies')
ORDER BY full_score DESC;

Handling Stopwords and Word Length

MySQL filters common words (stopwords) like “the”, “is”, “at” from searches. This can cause unexpected behavior:

-- This might return no results if all words are stopwords
SELECT title
FROM documentation
WHERE MATCH(content) AGAINST('the best way to cache');

-- Better: focus on meaningful terms
SELECT title
FROM documentation
WHERE MATCH(content) AGAINST('best cache strategy');

Check your stopword list:

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

The minimum word length (default: 3 for InnoDB, 4 for MyISAM) affects searches for short terms:

-- Check current settings
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
SHOW VARIABLES LIKE 'ft_min_word_len';

To change these settings, modify your MySQL configuration:

[mysqld]
innodb_ft_min_token_size=2
ft_min_word_len=2

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

ALTER TABLE documentation DROP INDEX ft_title_content;
ALTER TABLE documentation ADD FULLTEXT INDEX ft_title_content (title, content);
OPTIMIZE TABLE documentation;

Combining Full-Text with Traditional Queries

Full-text searches work alongside standard SQL conditions:

SELECT 
    id,
    title,
    category,
    created_at,
    MATCH(title, content) AGAINST('container orchestration') as relevance
FROM documentation
WHERE MATCH(title, content) AGAINST('container orchestration')
  AND category = 'DevOps'
  AND created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
ORDER BY relevance DESC, created_at DESC;

Use full-text search for the primary filtering, then apply additional criteria:

SELECT d.title, d.category, u.username,
       MATCH(d.title, d.content) AGAINST('API gateway patterns') as score
FROM documentation d
JOIN users u ON d.author_id = u.id
WHERE MATCH(d.title, d.content) AGAINST('API gateway patterns')
  AND u.role = 'architect'
ORDER BY score DESC;

Performance Considerations

Full-text indexes significantly improve search performance, but they come with trade-offs:

-- Without full-text index (slow on large tables)
SELECT * FROM documentation
WHERE content LIKE '%microservices%';

-- With full-text index (fast)
SELECT * FROM documentation
WHERE MATCH(content) AGAINST('microservices');

Monitor query performance using EXPLAIN:

EXPLAIN SELECT title 
FROM documentation 
WHERE MATCH(title, content) AGAINST('distributed systems');

For large result sets, use pagination:

SELECT id, title,
       MATCH(title, content) AGAINST('cloud architecture') as score
FROM documentation
WHERE MATCH(title, content) AGAINST('cloud architecture')
ORDER BY score DESC
LIMIT 20 OFFSET 0;

Practical Search Interface Implementation

Here’s a complete example for a documentation search feature:

DELIMITER //

CREATE PROCEDURE SearchDocumentation(
    IN search_query VARCHAR(255),
    IN result_limit INT,
    IN result_offset INT
)
BEGIN
    SELECT 
        id,
        title,
        category,
        SUBSTRING(content, 1, 300) as excerpt,
        created_at,
        MATCH(title, content) AGAINST(search_query) as relevance_score
    FROM documentation
    WHERE MATCH(title, content) AGAINST(search_query)
    ORDER BY relevance_score DESC
    LIMIT result_limit OFFSET result_offset;
    
    -- Get total count for pagination
    SELECT COUNT(*) as total_results
    FROM documentation
    WHERE MATCH(title, content) AGAINST(search_query);
END //

DELIMITER ;

-- Usage
CALL SearchDocumentation('database indexing', 10, 0);

This stored procedure provides both paginated results and total count, essential for building search interfaces with proper pagination controls.

Natural Language Mode excels at user-facing search features where simplicity matters. The automatic relevance ranking eliminates the need for complex query syntax, while the TF-IDF algorithm delivers intuitive results that match user expectations.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.