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:

• No FULLTEXT index — MATCH ... AGAINST requires a FULLTEXT index on the exact columns listed in MATCH(). Using columns not in the index returns an error or no results.
• Minimum word length — by default, MySQL ignores words shorter than 4 characters (innodb_ft_min_token_size = 3 for InnoDB, ft_min_word_len = 4 for MyISAM). Searching for “go”, “js”, “api”, “vue” returns nothing.
• Stopwords — MySQL has a built-in list of common words (“the”, “a”, “in”, “is”, etc.) that are excluded from full-text indexes. Searching for stopwords always returns empty.
• 50% threshold in natural language mode — in natural language mode (default), a word that appears in more than 50% of rows is treated as a stopword and ignored. Common on small datasets.
• InnoDB transaction isolation — after ALTER TABLE ... ADD FULLTEXT INDEX, existing rows aren’t immediately searchable. An OPTIMIZE TABLE or index rebuild is needed.
• Column mismatch — MATCH(title, content) requires a single FULLTEXT index that covers both title AND content together. Separate indexes don’t work.

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.

For related MySQL issues, see Fix: MySQL Index Not Being Used and Fix: MySQL Slow Query Optimization.