Comprehensive Guide to Analyzing and Optimizing Slow SQL Queries in MySQL

When a SQL statement runs slower than expected, the first step is to capture it using MySQL's slow query log. Enable the log by setting slow_query_log = 1 and defining slow_query_log_file , long_query_time , and related parameters. The log records execution time, lock time, rows examined, and more, allowing you to pinpoint problematic queries.

After capturing a slow query, use EXPLAIN to view its execution plan. Pay attention to the type column (e.g., ALL indicates a full table scan) and the rows estimate. Optimize by ensuring that filter conditions use indexed columns, avoiding functions on indexed fields, and preferring left‑most index prefixes. For multi‑table joins, examine join order and consider index coverage or query rewriting.

When the execution plan looks optimal but the query is still slow, enable profiling with SET SESSION profiling = 1 . After running the query, SHOW PROFILES and SHOW PROFILE FOR QUERY N reveal time spent in each phase (e.g., executing , sending data ), helping you locate I/O or CPU bottlenecks.

For deeper insight, turn on OPTIMIZER_TRACE ( SET SESSION optimizer_trace='enabled=on' ) and re‑run the statement. The JSON trace stored in information_schema.optimizer_trace shows how the optimizer evaluates indexes, estimates row counts, and chooses the final plan. Key fields such as ref_optimizer_key_uses and chosen_range_access_summary indicate which index was selected and its cost.

Database performance is also affected by the underlying I/O subsystem. Use Linux iostat -x 1 -m to monitor metrics like r/s , w/s , r_await , w_await , and avgqu-sz . Adjust InnoDB I/O parameters ( innodb_io_capacity , innodb_io_capacity_max ) to match the capabilities of SSDs or RAID arrays, and tune innodb_flush_log_at_trx_commit and sync_binlog to balance durability against write latency.

Other factors such as network latency, extremely large tables, or improper schema design can also cause slow queries. Verify network health with ping or traceroute, consider sharding or using middleware (e.g., Sharding‑JDBC, MyCat) for tables exceeding tens of millions of rows, and avoid SELECT * on wide tables.

In summary, a systematic approach—capturing slow queries, analyzing execution plans, profiling, inspecting optimizer traces, and monitoring I/O—allows you to identify the root cause of performance issues and apply targeted optimizations. Combining these database‑level techniques with basic DBA monitoring ensures stable and efficient MySQL operations.