Home / Databases / MySQL

DatabasesHow-toIntermediate

Finding slow MySQL queries with the slow query log and Performance Schema

Turn on the slow query log, summarise it with mysqldumpslow or pt-query-digest, use sys schema views for live analysis, then read EXPLAIN and add the right index.

Deepak Bhardwaj2 min read

Applies toMySQL 8.x, MariaDB 10.x, Amazon RDS/Aurora MySQL, Azure Database for MySQL, OCI MySQL HeatWave

1. Turn on the slow query log

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;                     -- seconds
SET GLOBAL log_queries_not_using_indexes = OFF;     -- ON is very noisy
SHOW VARIABLES LIKE 'slow_query_log_file';

Managed services: set these in the parameter group (RDS) or server parameters (Azure); the logs appear in the console or CloudWatch/Log Analytics when exported.

2. Summarise it

mysqldumpslow -s t -t 10 /var/lib/mysql/host-slow.log     # top 10 by total time
pt-query-digest /var/lib/mysql/host-slow.log > digest.txt # Percona Toolkit, more detail

Sort by total time (count × average), not just the slowest single query. A 200 ms query run 50,000 times an hour matters more than one 30-second report.

3. Live view without the log: sys schema

SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg, rows_sent_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC LIMIT 10;

SELECT * FROM sys.statements_with_full_table_scans ORDER BY total_latency DESC LIMIT 10;
SELECT * FROM sys.schema_unused_indexes;

rows_examined_avg far larger than rows_sent_avg → the query reads many rows to return a few. That's an index problem.

4. EXPLAIN

EXPLAIN FORMAT=TREE
SELECT * FROM orders WHERE customer_id = 42 AND status = 'OPEN' ORDER BY created_at DESC LIMIT 20;

EXPLAIN ANALYZE SELECT ...;     -- 8.0.18+, actually runs the query
In EXPLAIN Meaning
type: ALL Full table scan
key: NULL No index used
Using filesort Extra sort step; an index could deliver the order
Using temporary Temp table for GROUP BY/DISTINCT
rows very large Many rows examined

5. Add the right index

For the query above: equality columns first, then the sort column.

ALTER TABLE orders ADD INDEX idx_cust_status_created (customer_id, status, created_at), ALGORITHM=INPLACE, LOCK=NONE;

For very large tables in production, use an online schema change tool (gh-ost, pt-online-schema-change) to control load and replica lag.

6. Common query fixes

  • WHERE DATE(created_at) = '2026-10-22' → WHERE created_at >= '2026-10-22' AND created_at < '2026-10-23'.
  • LIKE '%term%' can't use a B-tree index. Consider a FULLTEXT index.
  • Comparing a string column to a number (WHERE phone = 98765) disables the index. Quote the value.
  • SELECT * on wide tables with ORDER BY ... LIMIT → select only needed columns.

Tip

Before adding an index, check sys.schema_redundant_indexes. Every extra index slows writes; replacing two overlapping indexes with one often helps both reads and writes.

Related fixes