How to Locate and Analyze Slow SQL Queries in MySQL

The article begins by introducing the importance of SQL optimization and the need to locate slow queries, then outlines two main methods: examining the MySQL slow‑query log and using SHOW PROCESSLIST to view currently running statements.

1. Locating Slow SQL via the Slow‑Query Log – The log records statements whose execution time exceeds long_query_time (default 10 s) and meet the min_examined_row_limit. To enable and configure the log, run:

mysql> set global slow_query_log = on;

mysql> set global long_query_time = 1;

Additional settings such as log_slow_admin_statements and log_queries_not_using_indexes can be turned on to capture admin statements and queries that do not use indexes. The log file path and name are obtained with:

mysql> SHOW GLOBAL VARIABLES LIKE "datadir";

mysql> SHOW GLOBAL VARIABLES LIKE "slow_query_log_file";

After locating the file (e.g., /data/mysql/data/3306/mysql‑slow.log), you can view recent entries with: tail -n5 /data/mysql/data/3306/mysql-slow.log The log shows fields such as Time, User@Host, Query_time, Lock_time, Rows_sent, and Rows_examined, which help assess query cost.

2. Locating Slow SQL via SHOW PROCESSLIST – When a slow query is still running, it may not appear in the log. Use: SHOW PROCESSLIST\G or SHOW FULL PROCESSLIST to see the full query text. Important columns include Time (execution duration) and Info (the SQL statement). Filtering by long Time values helps identify problematic queries.

3. Analyzing Slow Queries with EXPLAIN – After locating a slow statement, prepend EXPLAIN to obtain the execution plan. The article provides a sample table creation script and data‑loading procedure, then runs: EXPLAIN SELECT * FROM t1 WHERE b = 100; The EXPLAIN output includes columns such as id, select_type, table, type, possible_keys, key, rows, and Extra. The article explains the most important fields:

select_type – indicates query complexity (e.g., SIMPLE, PRIMARY, UNION, SUBQUERY).

type – shows join type, ordered from best to worst (system, const, eq_ref, ref, range, index, ALL).

key – the actual index used.

rows – estimated rows examined.

Extra – additional notes such as Using filesort, Using temporary, Using index condition, etc.

Tables summarizing possible values for select_type, type, and Extra are included, with brief explanations and example queries.

4. Summary – The article recaps that slow queries can be located via the slow‑query log or SHOW PROCESSLIST, and that EXPLAIN is a primary tool for analyzing execution plans. It notes that future sections will cover SHOW PROFILE and TRACE for deeper analysis.