How to Locate and Analyze Slow SQL Queries in MySQL

The article begins by introducing 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 see currently running statements.

1 Locate Slow SQL

Two solutions are presented:

Read the slow‑query log, which records statements whose execution time exceeds long_query_time and meet min_examined_row_limit.

Use SHOW PROCESSLIST (or SHOW FULL PROCESSLIST) to view active queries that may still be running.

1.1 Using the Slow‑Query Log

Steps to enable and configure the log: mysql> set global slow_query_log = on; Set the threshold: mysql> set global long_query_time = 1; Find the log file path and name:

mysql> show global variables like "datadir";

mysql> show global variables like "slow_query_log_file";

Example of viewing the last five lines of the log:

[root@mysqltest ~]# tail -n5 /data/mysql/data/3306/mysql-slow.log

The article explains each column of the log output (Time, User@Host, Query_time, etc.) and mentions tools like pt‑query‑digest or mysqldumpslow for further analysis.

1.2 Using SHOW PROCESSLIST

When a slow query is still executing, SHOW PROCESSLIST can reveal its Time and Info fields. Adding the FULL keyword shows the complete SQL text. mysql> show processlist\G The article highlights how to interpret the output to decide whether a query is slow.

2 Analyzing Slow Queries with EXPLAIN

After locating a slow statement, the EXPLAIN command provides its execution plan. The article creates two test tables (t1 and t2) and a stored procedure to insert data, then runs: mysql> explain select * from t1 where b=100; It lists and explains each column of the EXPLAIN output, emphasizing the important ones: select_type, type, key, rows, and Extra. Tables summarizing possible values for select_type, type, and Extra are provided with descriptions and examples.

2.1 select_type

Values such as SIMPLE, PRIMARY, UNION, SUBQUERY, DERIVED, etc., indicate the complexity of the query.

2.2 type

From best to worst performance: system, const, eq_ref, ref, index_merge, range, index, ALL.

2.3 Extra

Common flags like Using filesort, Using temporary, Using index, Using where, and others are explained with sample queries.

3 Summary

The article recaps the two ways to locate slow SQL (slow‑query log and SHOW PROCESSLIST) and the key EXPLAIN fields to focus on when analyzing performance. It notes that future sections will cover additional tools such as SHOW PROFILE and TRACE, and encourages readers to practice and share their optimization experiences.