A SaaS platform's product search was running 1.2 seconds per query on a 20M-row product catalog using MySQL LIKE patterns. After switching to InnoDB full-text search with proper FTS tuning and a dedicated auxiliary table approach, query time dropped to 45ms — a 26x improvement on the same hardware. Most teams don't realize MySQL's built-in FTS can handle this workload.
MySQL 8.4 LTS ships with improved FTS token size defaults and better auxiliary table management. This guide covers how to configure, tune, and maintain InnoDB full-text search for production workloads.
- InnoDB FTS indexes use auxiliary tables — understanding this architecture is key to tuning.
- Optimize FTS after bulk inserts with
OPTIMIZE TABLEto merge auxiliary tables. - Tune
innodb_ft_min_token_sizeandinnodb_ft_max_token_sizefor your language and content type. - Use
MATCH() AGAINST() IN BOOLEAN MODEfor phrase matching and relevance tuning.
InnoDB FTS Architecture
InnoDB FTS uses auxiliary tables to store inverted indexes. When you create a FULLTEXT index, MySQL creates 6 auxiliary FTS tables that store the token data. Understanding this helps you tune correctly:
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
description TEXT,
FULLTEXT KEY ft_search (name, description)
) ENGINE=InnoDB;
-- Check FTS auxiliary tables
SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_NAME LIKE 'FTS_%'
AND TABLE_SCHEMA = 'your_database';Core FTS Configuration
# my.cnf — FTS tuning for product search
[mysqld]
innodb_ft_min_token_size = 2 # Default 3 — allow 2-char tokens (e.g. "AI")
innodb_ft_max_token_size = 84 # Default 84 — usually fine
innodb_ft_cache_size = 8000000 # 8MB per table FTS cache (default 8MB)
innodb_ft_total_cache_size = 640000000 # 640MB total FTS cache (default 640MB)
innodb_ft_enable_stopword = 1 # Enable stopword filteringChanging innodb_ft_min_token_size requires dropping and recreating all FULLTEXT indexes, since existing tokens were indexed with the old minimum. Plan this as a maintenance operation with downtime or use a rolling replica rebuild.
Search Query Patterns
Natural Language Mode
-- Relevance-ranked search (default mode)
SELECT id, name, MATCH(name, description) AGAINST('wireless bluetooth headphones') AS relevance
FROM products
WHERE MATCH(name, description) AGAINST('wireless bluetooth headphones')
ORDER BY relevance DESC LIMIT 20;Boolean Mode for Precise Control
-- Boolean mode: + required, - excluded, * wildcard
SELECT id, name FROM products
WHERE MATCH(name, description) AGAINST('+wireless +bluetooth -wired head*' IN BOOLEAN MODE)
ORDER BY id LIMIT 20;
-- Phrase match with quotes
SELECT id, name FROM products
WHERE MATCH(name, description) AGAINST('"noise cancelling"' IN BOOLEAN MODE);Maintenance: Optimizing the FTS Index
After bulk inserts or updates, FTS auxiliary tables fragment. Run OPTIMIZE TABLE to merge them and rebuild the index — this dramatically improves query performance:
-- After bulk insert: optimize to merge FTS auxiliary tables
OPTIMIZE TABLE products;
-- Monitor FTS word cache usage
SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE LIMIT 5;
-- View the committed FTS index
SET GLOBAL innodb_ft_aux_table = 'your_database/products';
SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE LIMIT 10;Schedule OPTIMIZE TABLE during off-peak hours if your FTS table receives frequent updates. For write-heavy tables, run it nightly — fragmented auxiliary tables cause 3-5x slower FTS queries.
Custom Stopwords
-- Create custom stopword table
CREATE TABLE custom_stopwords (value VARCHAR(30) NOT NULL) ENGINE=InnoDB;
INSERT INTO custom_stopwords VALUES ('the'),('and'),('for'),('with'),('your');
-- Set in my.cnf
-- innodb_ft_server_stopword_table = 'your_database/custom_stopwords'
-- Requires restart and FULLTEXT index rebuild- Run
OPTIMIZE TABLEafter bulk inserts to merge FTS auxiliary tables and restore query performance. - Set
innodb_ft_min_token_size = 2for product search on short codes — but rebuild all FTS indexes after changing it. - Use boolean mode with
+required -excluded "phrase"operators for precise, production-grade search control. - Monitor auxiliary table fragmentation via
INNODB_FT_INDEX_CACHEand schedule optimization accordingly.
Working with JusDB on MySQL Search
JusDB evaluates MySQL FTS versus Elasticsearch or OpenSearch for your workload and helps implement whichever fits your scale, latency, and operational complexity requirements. We tune FTS configurations that deliver production-quality results without additional search infrastructure.