Fix: MySQL Full-Text Search Not Working — MATCH AGAINST Returns No Results

The Problem

A MySQL MATCH ... AGAINST query returns no results despite matching data existing:

-- Table has rows with 'programming' in the content column
SELECT * FROM articles WHERE MATCH(content) AGAINST('programming');
-- Returns: empty result set

-- Or with Boolean mode
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('+nodejs +tutorial' IN BOOLEAN MODE);
-- Returns: empty result set even though relevant articles exist

Or a short word search always returns nothing:

SELECT * FROM articles WHERE MATCH(title) AGAINST('go');
-- Returns: empty — 'go' is too short (default min word length is 4)

Or the full-text index is created but queries return incorrect results:

-- InnoDB table — FULLTEXT index just added
-- Existing data not indexed — new rows work, old rows don't appear
ALTER TABLE articles ADD FULLTEXT INDEX ft_content(content);
-- Existing rows missing from search results until index is rebuilt

Why This Happens

MySQL full-text search has several gotchas that cause silent failures. The most dangerous characteristic is that none of these failures produce errors — they all return empty result sets, making them indistinguishable from a legitimate “no results found” response at the application level.

The MATCH ... AGAINST function requires a FULLTEXT index on the exact combination of columns referenced in MATCH(). Using a single-column index when querying two columns, or querying a column not covered by any FULLTEXT index, returns an error in strict mode or silently returns nothing. After creating a new FULLTEXT index on an InnoDB table with existing data, the index may not immediately cover all rows. An OPTIMIZE TABLE is needed to flush pending changes from the index cache to the main index structure.

Word length filtering is the second most common silent failure. MySQL ignores words shorter than innodb_ft_min_token_size (default: 3 for InnoDB) or ft_min_word_len (default: 4 for MyISAM). Searches for “go”, “js”, “api”, or “vue” return nothing with default settings. The built-in stopword list also silently excludes common English words (“the”, “a”, “in”, “is”), and in natural language mode, any word appearing in more than 50% of rows is treated as a stopword — a trap that bites hard on small datasets where most test rows contain the same keyword.

Other causes:

• InnoDB transaction isolation — full-text search results reflect only committed data. Uncommitted writes are invisible.
• Column mismatch — MATCH(title, content) requires a single FULLTEXT index that covers both title AND content together. Separate indexes on each column do not satisfy the query.
• Charset mismatches — a column using latin1 indexed with FULLTEXT may silently drop non-ASCII characters during indexing.

In Production: Incident Lens

Full-text search failures in production are uniquely deceptive because they never throw errors. The query succeeds — it just returns zero rows. Your application renders “No results found” and moves on. There is no 500, no exception, no log entry. Users report “search is broken” through support tickets, and the first instinct is to blame the frontend.

How it surfaces: A deploy adds a FULLTEXT index to a new column combination, or a DBA changes innodb_ft_min_token_size in a config update and restarts MySQL without rebuilding indexes. Alternatively, a table grows past a threshold where a frequently searched term now appears in more than 50% of rows, silently activating the natural-language-mode stopword rule. In all cases, search works for some queries but not others, making the bug intermittent from the user’s perspective.

Blast radius: The search feature is completely or partially broken. If search drives product discovery (e-commerce, documentation sites, content platforms), the business impact is immediate — users can’t find products, articles, or records. Unlike a crash, there’s no automatic recovery. The bad state persists until someone investigates.

Monitoring signals:

• Search result count distribution: alert if the percentage of zero-result queries exceeds a baseline (e.g., spikes from 10% to 60%)
• Query latency: full-text queries on a missing or corrupted index may be faster than expected (scanning nothing) or much slower (falling back to a table scan)
• SHOW INDEX FROM <table> WHERE Index_type = 'FULLTEXT' in a periodic health check — confirms the index exists and covers the expected columns

Recovery sequence: Run OPTIMIZE TABLE <table> to rebuild the FULLTEXT index. If ft_min_word_len or innodb_ft_min_token_size was changed, restart MySQL and then OPTIMIZE TABLE. For the 50% threshold issue, switch affected queries to IN BOOLEAN MODE. For an emergency, fall back to LIKE '%term%' queries (slow but functional) while the index is rebuilt. REPAIR TABLE works for MyISAM tables specifically.

Postmortem preventives: Add integration tests that assert specific search terms return expected document IDs. Include FULLTEXT index validation in deploy checklists. Monitor zero-result search rates as a first-class SLI.

Fix 1: Create the Correct FULLTEXT Index

MATCH() must reference columns covered by a FULLTEXT index:

-- Single column index
CREATE FULLTEXT INDEX ft_content ON articles(content);

-- Multi-column index — MATCH(title, content) requires BOTH columns in ONE index
CREATE FULLTEXT INDEX ft_title_content ON articles(title, content);

-- WRONG — two separate indexes don't work with MATCH(title, content)
CREATE FULLTEXT INDEX ft_title ON articles(title);
CREATE FULLTEXT INDEX ft_content ON articles(content);
-- This causes: ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

-- Verify existing indexes
SHOW INDEX FROM articles WHERE Index_type = 'FULLTEXT';

Create the index at table creation time:

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT INDEX ft_search(title, content)   -- Created with the table
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Add a FULLTEXT index to an existing table:

-- Add index (locks table on MyISAM, online on InnoDB 5.6+)
ALTER TABLE articles ADD FULLTEXT INDEX ft_search(title, content);

-- After adding to an InnoDB table with existing data, rebuild to ensure all rows indexed
OPTIMIZE TABLE articles;

Fix 2: Fix Minimum Word Length

Words shorter than innodb_ft_min_token_size (default: 3) are not indexed:

-- Check current settings
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';   -- InnoDB, default: 3
SHOW VARIABLES LIKE 'ft_min_word_len';            -- MyISAM, default: 4

-- 'go', 'js', 'api' are 2 characters — not indexed with default settings
SELECT * FROM articles WHERE MATCH(title) AGAINST('go');   -- Always empty

Change the minimum word length:

# my.cnf / my.ini — lower the minimum word length
[mysqld]
innodb_ft_min_token_size = 2   # For InnoDB (minimum: 1)
ft_min_word_len = 2            # For MyISAM (minimum: 1)

After changing the configuration, restart MySQL and rebuild all FULLTEXT indexes:

-- After restarting MySQL
-- Rebuild FULLTEXT indexes to apply the new word length
OPTIMIZE TABLE articles;

-- Or drop and recreate the index
ALTER TABLE articles DROP INDEX ft_search;
ALTER TABLE articles ADD FULLTEXT INDEX ft_search(title, content);

Alternative — use Boolean mode with wildcards for short terms:

-- Boolean mode with wildcard catches short words without config changes
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('go*' IN BOOLEAN MODE);
-- Matches: 'go', 'golang', 'google', etc.

Fix 3: Handle the 50% Threshold

In natural language mode, words in more than 50% of rows are excluded:

-- Small test table with 5 rows, all containing 'tutorial'
-- 'tutorial' is in 100% of rows → treated as stopword → no results

SELECT * FROM articles WHERE MATCH(content) AGAINST('tutorial');
-- Returns empty — 'tutorial' exceeds 50% threshold

-- Fix 1 — use Boolean mode (no 50% threshold)
SELECT * FROM articles
WHERE MATCH(content) AGAINST('+tutorial' IN BOOLEAN MODE);

-- Fix 2 — add more rows to push 'tutorial' below 50%
-- (impractical for production, but explains test failures)

-- Fix 3 — use WITH QUERY EXPANSION for natural language mode
SELECT * FROM articles
WHERE MATCH(content) AGAINST('tutorial' WITH QUERY EXPANSION);
-- Expands the search using top results — avoids 50% issue

Check relevance scores to see why rows are excluded:

SELECT
    id,
    title,
    MATCH(title, content) AGAINST('tutorial' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
ORDER BY relevance DESC;

-- Rows with relevance = 0 are excluded from results
-- Low relevance might indicate the word is treated as a stopword

Fix 4: Use Boolean Mode Effectively

Boolean mode gives more control over search behavior:

-- Boolean mode operators:
-- + = word must be present
-- - = word must NOT be present
-- ~ = word reduces relevance (soft exclude)
-- * = wildcard (at end of word only)
-- " " = exact phrase
-- > = higher relevance boost
-- < = lower relevance boost
-- () = grouping

-- Must contain 'nodejs' AND 'tutorial'
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+nodejs +tutorial' IN BOOLEAN MODE);

-- Must contain 'nodejs', must NOT contain 'error'
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+nodejs -error' IN BOOLEAN MODE);

-- Must contain 'react', optionally 'hooks' (boosts relevance)
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+react hooks' IN BOOLEAN MODE);

-- Exact phrase
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('"node.js tutorial"' IN BOOLEAN MODE);

-- Prefix search — all words starting with 'java'
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('java*' IN BOOLEAN MODE);
-- Matches: java, javascript, javadoc, etc.

-- Boost title matches over content matches
SELECT *,
    MATCH(title) AGAINST('+nodejs' IN BOOLEAN MODE) * 3 +
    MATCH(content) AGAINST('+nodejs' IN BOOLEAN MODE) AS relevance
FROM articles
WHERE
    MATCH(title) AGAINST('+nodejs' IN BOOLEAN MODE) OR
    MATCH(content) AGAINST('+nodejs' IN BOOLEAN MODE)
ORDER BY relevance DESC;

Fix 5: Manage Custom Stopwords

Remove words from the stopword list that are important to your domain:

-- Check if a word is a stopword
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD
WHERE value = 'after';

-- Create a custom stopword table (InnoDB)
CREATE TABLE my_stopwords (value VARCHAR(30)) ENGINE=InnoDB;

-- Populate with only the words you want to exclude
-- (Empty table = no stopwords)
-- INSERT INTO my_stopwords VALUES ('the'), ('a'), ('an');

-- Tell MySQL to use your custom stopword list
SET GLOBAL innodb_ft_server_stopword_table = 'mydb/my_stopwords';

-- Rebuild indexes for the change to take effect
ALTER TABLE articles DROP INDEX ft_search;
ALTER TABLE articles ADD FULLTEXT INDEX ft_search(title, content);

Configure at startup for permanent change:

# my.cnf
[mysqld]
innodb_ft_server_stopword_table = mydb/my_stopwords
innodb_ft_enable_stopword = ON   # Set OFF to disable all stopwords

Fix 6: Combine Full-Text Search with Other Filters

Full-text search and standard WHERE conditions can be combined:

-- Full-text search + date filter + category filter
SELECT
    id,
    title,
    created_at,
    MATCH(title, content) AGAINST('+nodejs +performance' IN BOOLEAN MODE) AS relevance
FROM articles
WHERE
    MATCH(title, content) AGAINST('+nodejs +performance' IN BOOLEAN MODE)
    AND created_at >= '2024-01-01'
    AND category = 'backend'
    AND status = 'published'
ORDER BY relevance DESC
LIMIT 10;

-- IMPORTANT: The MATCH() condition must appear in both SELECT and WHERE
-- if you want both relevance scores and filtering

Pagination with full-text search:

-- Total count for pagination
SELECT COUNT(*) AS total
FROM articles
WHERE MATCH(title, content) AGAINST('+nodejs' IN BOOLEAN MODE);

-- Page results
SELECT id, title,
    MATCH(title, content) AGAINST('+nodejs' IN BOOLEAN MODE) AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('+nodejs' IN BOOLEAN MODE)
ORDER BY relevance DESC
LIMIT 10 OFFSET 20;   -- Page 3 (0-indexed pages of 10)

Fix 7: Debug Full-Text Index Issues

Diagnose why specific rows aren’t appearing in results:

-- Check if a word is indexed for a specific row (InnoDB)
-- Enable aux tables to see what's in the FT index
SET GLOBAL innodb_ft_aux_table = 'mydb/articles';

-- Check the index for a specific term
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE
WHERE word = 'nodejs';

-- Check committed index
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE
WHERE word = 'nodejs';

-- If a word appears in INNODB_FT_INDEX_CACHE but not in INNODB_FT_INDEX_TABLE:
-- Run OPTIMIZE TABLE to flush the cache to the main index
OPTIMIZE TABLE articles;

Compare LIKE vs FULLTEXT to find discrepancies:

-- Find rows that LIKE finds but FULLTEXT doesn't
SELECT id, title FROM articles
WHERE content LIKE '%nodejs%'
AND NOT MATCH(title, content) AGAINST('+nodejs' IN BOOLEAN MODE);

-- Common reasons a row appears in LIKE but not FULLTEXT:
-- 1. 'nodejs' is below minimum word length (6 chars — fine, but check your config)
-- 2. Row was inserted before the FULLTEXT index was created and index needs rebuild
-- 3. 'nodejs' is in the stopword list (unlikely but check)
-- 4. The content column in MATCH() doesn't match the indexed column

Still Not Working?

utf8mb4 and full-text search — MySQL supports full-text search with utf8mb4 encoding but requires the column charset and the server charset to match. A charset mismatch can cause indexing failures for non-ASCII characters.

Partial word search without wildcards — full-text search by default doesn’t match substrings. AGAINST('node') won’t match “nodejs”. Use AGAINST('node*' IN BOOLEAN MODE) for prefix matching, or switch to Elasticsearch/OpenSearch for substring matching at scale.

InnoDB transaction visibility — full-text search results reflect committed data. Changes inside an uncommitted transaction aren’t visible to full-text queries until committed.

Result ordering — MATCH ... AGAINST returns results in relevance order only when no explicit ORDER BY is present. Once you add ORDER BY created_at DESC, relevance ordering is lost unless you explicitly sort by the relevance score in SELECT.

ngram parser for CJK languages — MySQL’s default full-text parser tokenizes on whitespace and punctuation, which fails for Chinese, Japanese, and Korean text that has no word boundaries. Enable the built-in ngram parser when creating the index: CREATE FULLTEXT INDEX ft_content ON articles(content) WITH PARSER ngram;. Set ngram_token_size (default: 2) in my.cnf before creating the index.

FULLTEXT on partitioned tables — MySQL does not support FULLTEXT indexes on partitioned InnoDB tables. If you partition a table that had a FULLTEXT index, the index is silently dropped. Check SHOW INDEX FROM <table> after partition changes to confirm the index still exists.

MyISAM concurrent writes corrupt the index — MyISAM FULLTEXT indexes are not crash-safe. A server crash during a write can leave the index in an inconsistent state where some documents are missing. Run REPAIR TABLE <table> QUICK after an unclean shutdown, then OPTIMIZE TABLE to rebuild.

For related MySQL issues, see Fix: MySQL Index Not Being Used, Fix: MySQL Slow Query Optimization, Fix: MySQL Deadlock Detected, and Fix: MySQL Lock Wait Timeout Exceeded.