Finding Slow Queries in MySQL and MariaDB

Introduction¶

When a database "feels slow", guessing is expensive. The slow query log plus a digest tool tells you exactly which queries cost the most total time — which is usually a handful you can fix in an afternoon. This post is the process, start to finish.

Concepts¶

The slow query log records any statement that runs longer than long_query_time seconds (and, optionally, queries that don't use an index). On its own it's a wall of text; a digest groups queries by shape (literals replaced with ?) and ranks them by total time, not just the single slowest run. A query that takes 50 ms but runs 10,000 times an hour matters more than one that takes 3 s once a day.

Hands-on¶

Turn it on without a restart¶

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 0.5;        -- seconds; start higher, then lower
SET GLOBAL log_queries_not_using_indexes = 'ON';   -- noisy but revealing
SET GLOBAL log_output = 'FILE';

Persist it in my.cnf once you're happy:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

Let it collect for a representative period — a peak hour, or a full day.

Summarise it¶

Percona's pt-query-digest is the standard:

pt-query-digest /var/log/mysql/slow.log | less

The top of the report ranks query classes by total time. Each block shows call count, mean/95^th/max latency, rows examined vs rows sent, and a sample statement. If you can't install Percona Toolkit, mysqldumpslow -s t -t 10 /var/log/mysql/slow.log is a weaker built-in alternative.

Focus on classes where Rows examined ≫ Rows sent — the query is scanning a lot to return a little, which almost always means a missing or unusable index.

EXPLAIN the worst offender¶

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

Read the type and key columns:

Extra: Using filesort or Using temporary on a hot query is a sign the ORDER BY / GROUP BY can't use an index.

Add the right index¶

For the query above, a composite index that matches the WHERE equality columns then the ORDER BY column:

ALTER TABLE orders ADD INDEX idx_cust_status_created (customer_id, status, created_at);

Column order in a composite index matters: equality predicates first, then the range/sort column. The optimiser can use a leftmost prefix, so this index also helps WHERE customer_id = ? alone.

Re-run EXPLAIN — type should move to ref and the filesort should disappear.

Verification and troubleshooting¶

• The log is empty — long_query_time is too high, or min_examined_row_limit is set, or the app connects with SET SESSION long_query_time overriding the global. Also: SET GLOBAL doesn't affect existing connections; reconnect or restart the app's pool.
• Digest shows one giant COMMIT class — that's lock/replication wait time, not query time. Look at SHOW ENGINE INNODB STATUS and lock waits, not indexes.
• Adding the index didn't help — the query uses a function on the column (WHERE DATE(created_at) = ?), a leading wildcard (LIKE '%foo'), or an implicit type cast (WHERE varchar_col = 123). All of these make an index unusable. Rewrite the predicate.
• Index added, writes got slower — every index is maintained on every INSERT/UPDATE/DELETE. Drop unused ones; sys.schema_unused_indexes lists candidates.
• Rows examined is fine but latency is still high — could be row locking, a cold buffer pool (check innodb_buffer_pool_size vs data size), or the result set is genuinely large and network-bound.
• Different plan in prod than staging — table statistics are stale. ANALYZE TABLE orders;.

Related tools and reading¶

• On-site: Port Checker for connectivity checks, Hash Generator.
• Related posts: Reading an EXPLAIN plan, InnoDB buffer pool sizing.