Slow Query Accumulation from Missing Indexes
warningQueries that perform full table scans due to missing or inefficient indexes accumulate in the slow query log. These queries consume excessive CPU, I/O, and time, degrading overall database throughput and user experience. The problem scales with data growth and query frequency.
Monitor Slow_queries metric and configure long_query_time threshold appropriately (default 10s, often tuned lower). Increasing Slow_queries count combined with high CPU utilization and elevated ReadIOPS suggests missing indexes. Use EXPLAIN on slow queries to identify 'type: ALL' (full table scan) and high 'rows' examined. Query performance_schema or sys schema for top queries by runtime.
Enable slow query log if not already active. Analyze slow queries using sys.statements_with_runtimes_in_95th_percentile or mysql.slow_log. Run EXPLAIN on identified queries to find missing indexes. Add appropriate indexes (CREATE INDEX) on columns used in WHERE, JOIN, and ORDER BY clauses. Re-test query performance. Monitor index usage with performance_schema to avoid over-indexing.