Optimize full-text search in Amazon RDS for MySQL and Amazon Aurora MySQL

This article provides comprehensive guidance on optimizing full-text search (FTS) performance in Amazon RDS for MySQL and Aurora MySQL-Compatible Edition.

• FTS indexes require regular maintenance unlike standard B-tree indexes due to complex inverted index architecture
• Increase innodb_ft_sort_pll_degree (1-16 threads) to accelerate FTS index creation on large tables
• Raise innodb_sort_buffer_size to 64 MB to improve large table indexing performance
• Unmaintained FTS causes semaphore waits, slower DML operations, degraded search performance
• Schedule OPTIMIZE TABLE during low-traffic periods using innodb_optimize_fulltext_only parameter
• Configure innodb_ft_cache_size to 32-64 MB for heavy write workloads (default: 8 MB)
• Monitor Aurora_fts_cache_memory_used, fragmentation percentage, and RollbackSegmentHistoryListLength metrics
• Run OPTIMIZE TABLE multiple times based on index size (100-5000+ iterations for large datasets)
• Create dedicated low-privilege maintenance user accounts for scheduled optimization tasks

Regular FTS maintenance prevents database crashes, duplicate key errors, and search inconsistencies by proactively managing index fragmentation and cache accumulation.