MySQL Slow Query Analysis and Optimization Guide

Slow Query Identification

Enable Slow Query Log

SHOW VARIABLES LIKE 'slow_query_log%'

SET global slow_query_log = ON;
SET global slow_query_log_file = 'h122-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;

long_query_time

Specifies the threshold for slow queries in seconds, default value is 10 seconds.

log_queries_not_using_indexes

Controls whether to record queries not using indexes.

View Slow Query Log

Log contains:

  • time: Timestamp
  • User@Host: User and host executing query
  • Query_time: Total execution time of SQL statement
  • Lock_time: Time waiting for table lock
  • Rows_sent: Number of returned records
  • Rows_examined: Total number of scanned records

mysqldumpslow

perl mysqldumpslow.pl -t 5 -s xxx-slow.log

Slow Query Optimization

Common Optimization Solutions

Only Select Required Columns and Rows

  • Write complete SELECT list, don’t use SELECT *
  • Add proper WHERE clauses

Create Indexes for Filtering and Joining

  • Prioritize creating indexes on columns appearing in WHERE/JOIN/ORDER BY/GROUP BY

Avoid Function Wrapping Columns and Implicit Type Conversion

Avoid Leading Wildcards in Fuzzy Queries

  • LIKE ‘%abc’ cannot use regular B-Tree indexes

Pagination Optimization (Deep Pagination Killer)

SELECT id, name FROM t
WHERE id > ?
ORDER BY id
LIMIT 20;

Covering Index

  • Let query complete only at index level, avoid table lookup

Reasonably Split OR / Prefer Using EXISTS

SELECT ... FROM t WHERE type=1 AND cond...
UNION ALL
SELECT ... FROM t WHERE type=2 AND cond...