How to Locate and Analyze Slow SQL Queries in MySQL

The article begins by introducing SQL optimization and explains why locating slow queries is essential for improving application performance.

1. Locating Slow SQL

Two main methods are presented:

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

Use SHOW PROCESSLIST to view currently running queries, useful when a slow query is still executing and not yet logged.

To enable and configure the slow‑query log:

mysql> set global slow_query_log = on;

mysql> set global long_query_time = 1;

Determine the log file location and name:

mysql> SHOW GLOBAL VARIABLES LIKE "datadir";

mysql> SHOW GLOBAL VARIABLES LIKE "slow_query_log_file";

Example of viewing recent entries:

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

Key fields in the log (Time, User@Host, Query_time, Lock_time, Rows_sent, Rows_examined) are explained, and the article notes that tools like pt‑query‑digest or mysqldumpslow can further process the log.

2. Analyzing Slow Queries with EXPLAIN

After locating a slow statement, EXPLAIN provides the execution plan, showing whether indexes are used, estimated rows scanned, and other performance‑relevant details.

Example: mysql> EXPLAIN SELECT * FROM t1 WHERE b=100; The article lists the most important columns of the EXPLAIN output: select_type, type, key, rows, and Extra, and explains their meanings.

2.1 select_type

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

2.2 type

Connection types range from system (best) to ALL (full table scan), with intermediate values like const, ref, range, and index.

2.3 Extra

Common Extra values (e.g., Using filesort, Using temporary, Using index condition) highlight additional operations that may affect performance.

Sample tables used for demonstration are created with the following statements:

CREATE DATABASE muke; USE muke; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'record creation time', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'record update time', KEY idx_a (a), KEY idx_b (b)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP PROCEDURE IF EXISTS insert_t1; DELIMITER ;; CREATE PROCEDURE insert_t1() BEGIN DECLARE i INT; SET i=1; WHILE i<=1000 DO INSERT INTO t1(a,b) VALUES(i,i); SET i=i+1; END WHILE; END;; DELIMITER ; CALL insert_t1();

Additional tables t2 are created with CREATE TABLE t2 LIKE t1; and populated via INSERT INTO t2 SELECT * FROM t1;.

3. Summary

The article recaps the two ways to locate slow SQL (slow‑query log and SHOW PROCESSLIST) and emphasizes that the EXPLAIN output fields select_type, type, key, rows, and Extra are the primary focus for performance tuning. It mentions that future sections will cover SHOW PROFILE and TRACE for deeper analysis.