Use when a query is slow and you need to diagnose, enabling the slow query log, analyzing aggregated samples with mariadb-dumpslow or pt-query-digest, profiling via performance_schema, detecting missing indexes from EXPLAIN, or interpreting Using filesort and Using temporary signals. Prevents the common mistake of trusting random one-off timings, leaving the slow query log disabled in production, setting long_query_time too high (10s) and missing sub-second issues, treating Using filesort as harmless, ignoring file-sort vs index-sort distinction, or using deprecated SHOW PROFILE instead of performance_schema. Covers slow_query_log + long_query_time + log_slow_verbosity configuration, mariadb-dumpslow vs pt-query-digest aggregation, performance_schema events_statements_summary_by_digest and events_statements_history_long, the 10.11 variable-rename to log_slow_query_*, missing-index detection patterns, file-sort vs index-sort with descending indexes (10.8+), Using temporary signal, EXPLAIN combined with ANALYZE
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Use when a query is slow and you need to diagnose, enabling the slow query log, analyzing aggregated samples with mariadb-dumpslow or pt-query-digest, profiling via performance_schema, detecting missing indexes from EXPLAIN, or interpreting Using filesort and Using temporary signals. Prevents the common mistake of trusting random one-off timings, leaving the slow query log disabled in production, setting long_query_time too high (10s) and missing sub-second issues, treating Using filesort as harmless, ignoring file-sort vs index-sort distinction, or using deprecated SHOW PROFILE instead of performance_schema. Covers slow_query_log + long_query_time + log_slow_verbosity configuration, mariadb-dumpslow vs pt-query-digest aggregation, performance_schema events_statements_summary_by_digest and events_statements_history_long, the 10.11 variable-rename to log_slow_query_*, missing-index detection patterns, file-sort vs index-sort with descending indexes (10.8+), Using temporary signal, EXPLAIN combined with ANALYZE FORMAT=JSON, Galera-aware slow-log handling. Keywords: slow query, slow_query_log, log_slow_query, long_query_time, log_slow_query_time, log_slow_verbosity, log_slow_filter, log_slow_rate_limit, min_examined_row_limit, mariadb-dumpslow, pt-query-digest, performance_schema, events_statements_summary_by_digest, SHOW PROFILE deprecated, Using filesort, Using temporary, missing index, full scan, type=ALL, key=NULL, file-sort vs index-sort, descending index, ANALYZE FORMAT JSON, my query is slow, slow query log location, why is my query slow, how to find slow queries, my page is slow, database is slow, queries timing out
license
MIT
compatibility
Designed for Claude Code. Requires MariaDB 10.6-LTS, 10.11-LTS, 11.x, 12.x.
metadata
{"author":"OpenAEC-Foundation","version":"1.0"}
MariaDB Slow Query Diagnosis
How to enable, capture, aggregate, and act on slow-query data. Covers the slow query log, performance_schema digests, EXPLAIN / ANALYZE signals, and the file-sort versus index-sort distinction.
Quick Reference
ALWAYS enable the slow query log in production. slow_query_log = ON with long_query_time = 0.5 (or smaller) catches sub-second issues that 10 (the default) hides.
ALWAYS set log_slow_verbosity = query_plan,explain (10.5+) so each slow-log entry carries the optimizer plan and an inline EXPLAIN. Without this you see SQL text but cannot decide why it was slow.
ALWAYS prefer pt-query-digest (Percona Toolkit) over mariadb-dumpslow for aggregation. pt-query-digest fingerprints by digest and gives 95th-percentile, mean, total time, and lock-time per query family. mariadb-dumpslow only counts.
ALWAYS aggregate at the FINGERPRINT level. Two queries that differ only in literal values are the same slow-query family. pt-query-digest and events_statements_summary_by_digest normalise them already.
NEVER rely on SHOW PROFILE / SHOW PROFILES. They are deprecated since MariaDB 5.6.7 / 10.0. Use performance_schema events tables instead.
NEVER trust a single EXPLAIN timing as proof. EXPLAIN does not execute the query. ALWAYS use ANALYZE FORMAT=JSON SELECT ... (10.1+) to compare estimated rows against observed r_rows.
NEVER treat Using filesort as a fatal signal on its own. It is fatal on millions of rows or hot OLTP paths ; it is fine on small result sets. ALWAYS combine the Using filesort flag with rows in EXPLAIN before deciding to redesign.
NEVER log slow queries to TABLE in a production write-heavy workload. log_output = TABLE writes every entry to mysql.slow_log, doubling write amplification. Keep log_output = FILE (the default) and use file rotation.
NEVER raise long_query_time to "reduce noise". Lower it instead, and use log_slow_rate_limit or min_examined_row_limit to thin the volume while still catching short-but-frequent killers.
NOTE : From 10.11 the variables are renamed. slow_query_log is now , is , is , is . The old names remain as aliases. Use the new names in 10.11+ config ; both work.
log_slow_query
long_query_time
log_slow_query_time
slow_query_log_file
log_slow_query_file
min_examined_row_limit
log_slow_min_examined_row_limit
Decision Tree
A query / page / report is slow.
|
+-- Is the slow query log already enabled ?
| SELECT @@slow_query_log, @@long_query_time, @@log_slow_verbosity, @@log_output;
| |
| NO : enable it (see Configuration below) and wait for representative traffic.
| YES : continue.
|
+-- Goal : find which queries are slow ?
| |
| +-- Have a slow-log file already ?
| | YES : run pt-query-digest /var/log/mysql/mariadb-slow.log
| | Look at the top 10 by Total Time.
| | NO : enable + wait, then come back.
| |
| +-- No file (log_output=TABLE) ?
| SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 20;
|
+-- Goal : profile ONE specific query right now ?
| |
| +-- Run EXPLAIN first :
| | EXPLAIN SELECT ...
| | Look at type (ALL = full scan) and key (NULL = no index used).
| |
| +-- Run ANALYZE FORMAT=JSON for real numbers (10.1+, SELECT only) :
| | ANALYZE FORMAT=JSON SELECT ...
| | Compare rows (estimated) vs r_rows (actual).
| |
| +-- Want time per stage (parse, lock, send) ?
| Query performance_schema.events_statements_history_long,
| filtered on EVENT_NAME like 'statement/sql/select' and SQL_TEXT.
|
+-- Goal : long-term top-N across the whole workload ?
SELECT digest_text, count_star, sum_timer_wait/1e12 AS total_s,
avg_timer_wait/1e9 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 20;
Configuration
Minimum production-grade config (10.6+ and 10.11+ alias names) :
All slow-log variables are dynamic. ALWAYS use SET GLOBAL for runtime tuning so a restart is not required.
-- 10.6+ : flip on without restartSETGLOBAL slow_query_log =ON;
SETGLOBAL long_query_time =0.5;
SETGLOBAL log_slow_verbosity ='query_plan,explain';
SETGLOBAL log_queries_not_using_indexes =ON;
-- 10.6+ : flush + open new file (e.g. after rotation)
FLUSH SLOW LOGS;
-- 10.11+ : new names work side-by-sideSETGLOBAL log_slow_query =ON;
SETGLOBAL log_slow_query_time =0.5;
long_query_time accepts fractional seconds (microsecond resolution). 0.5 is a safer baseline than 10 for any OLTP system.
Per-session tracing for ad-hoc debugging :
-- 10.6+ : trace ONE session only, do not pollute global logSET SESSION long_query_time =0;
SET SESSION log_slow_verbosity ='query_plan,explain';
SET SESSION slow_query_log =ON;
-- run the suspect query, then turn offSET SESSION slow_query_log = OFF;
See references/methods.md for the full variable matrix (defaults, scope, dynamic flag, version) and the log_slow_filter flag set.
What the Slow Log Records per Entry
Each entry begins with # User@Host: and is followed by three header lines plus the SQL text. Per the KB :
# User@Host: app[app] @ localhost []
# Thread_id: 17 Schema: app QC_hit: No
# Query_time: 1.234567 Lock_time: 0.000234 Rows_sent: 12 Rows_examined: 1245678
SET timestamp=1716123456;
SELECT ... FROM ... WHERE ...;
When log_slow_verbosity includes explain, the entry also carries an inline # explain: block with the optimizer's plan. This is only emitted when log_output = FILE (per KB : "EXPLAIN output will only be recorded if the slow query log is written to a file").
Rows_examined is the key number. A query reporting Rows_sent: 12 but Rows_examined: 1245678 is almost always a missing-index problem regardless of total time.
Aggregating with pt-query-digest
pt-query-digest is the de-facto aggregator. It fingerprints queries (normalises literals to ?), groups by digest, and ranks by total time, count, mean, and 95th percentile.
# Percona Toolkit installed separately ; works against a MariaDB slow-log
pt-query-digest /var/log/mysql/mariadb-slow.log > digest.txt
head -200 digest.txt
Top section : Profile table with rank, Response time, calls, R/Call (mean), V/M (variance-to-mean). Then per-query block with the canonical example, EXPLAIN-able SQL, histograms, and the literal sample.
mariadb-dumpslow is the bundled minimal alternative :
# 10.6+ : groups identical-after-normalisation queries, sorts by total time
mariadb-dumpslow -s t /var/log/mysql/mariadb-slow.log | head -50
Use mariadb-dumpslow when Percona Toolkit is not available. ALWAYS prefer pt-query-digest when it is.
Aggregating with performance_schema (No External Tool)
performance_schema ships disabled by default. Enabling requires a restart :
# my.cnf[mariadb]performance_schema = ON
Once enabled, the digest table is the all-workload top-N source :
-- 10.6+ : top-20 by total time, normalised by digestSELECTLEFT(digest_text, 80) AS query,
count_star AS calls,
ROUND(sum_timer_wait/1e12, 2) AS total_s,
ROUND(avg_timer_wait/1e9, 2) AS avg_ms,
ROUND(max_timer_wait/1e9, 2) AS max_ms,
sum_rows_examined /NULLIF(count_star, 0) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDERBY sum_timer_wait DESC
LIMIT 20;
events_statements_history_long keeps the most recent N completed statements (default 10000) with per-statement wait, lock-time, errors, and stage breakdown. Use it for the "what was happening at 14:32" question.
See references/methods.md for the relevant table list and the columns to project.
EXPLAIN Signals That Predict Slow
EXPLAIN column / value
Meaning
Action
type = ALL
Full table scan
Add an index on the WHERE columns, or rewrite the predicate
type = index
Full index scan (better than ALL, still scans every index leaf)
Add a more selective index
key = NULL
No index used
Same as above
rows >> matched
Optimizer estimates many rows for few results
Bad statistics : run ANALYZE TABLE. Confirm with ANALYZE FORMAT=JSON (compare rows vs r_rows)
Extra : Using filesort
An extra sort pass after the join
Match ORDER BY to an existing index, or add a descending index (10.8+)
Extra : Using temporary
Intermediate temp table for GROUP BY / DISTINCT / ORDER BY on non-driver table
Add a composite index covering GROUP BY / ORDER BY, or rewrite
Extra : Using index
Covering index ; no row fetch
This is GOOD ; do not "fix" it
Extra : Using index condition
ICP pushdown
GOOD ; predicate pushed to engine
Extra : Using join buffer (Block Nested Loop)
No usable index for the join
Add an index on the join column of the inner table
EXPLAIN gives ESTIMATES. ANALYZE FORMAT=JSON SELECT ... (10.1+) gives ACTUAL numbers. Use it to validate before refactoring :
-- 10.6+ : actual rows + actual time per node ; SELECT-safe
ANALYZE FORMAT=JSON SELECT ... FROM ... WHERE ...;
WARNING : ANALYZE UPDATE and ANALYZE DELETE actually execute the mutation (per KB). ALWAYS run ANALYZE FORMAT=JSON SELECT only, never ANALYZE FORMAT=JSON UPDATE/DELETE for a "dry run" : there is no dry run.
File-Sort vs Index-Sort
Using filesort does NOT mean "writing to disk". It means the optimizer chose a sort step that is not satisfied by an index. The sort may be in-memory (small result) or on-disk (large result), but the EXPLAIN flag is the same.
ALWAYS distinguish three cases :
ORDER BY columns match a leading prefix of an existing index, in the same direction : NO Using filesort ; index-sort is used. Best.
ORDER BY columns match an index but in the OPPOSITE direction across columns : Using filesort appears UNTIL you add a descending index. From 10.8+, MariaDB supports descending indexes natively (CREATE INDEX idx ON t (a ASC, b DESC)).
ORDER BY columns are not in any index, or mix ASC/DESC in a way no index covers : Using filesort is unavoidable. The fix is to add a covering composite index, or to redesign the query.
Detection of "filesort going to disk" : enable log_slow_verbosity with query_plan and look for Tmp_table_on_disk: Yes in the slow-log block. Alternatively, query Sort_merge_passes from SHOW STATUS ; a steady non-zero rate means disk-spill is regular.
-- 10.8+ : descending index removes Using filesort on ORDER BY a, b DESCCREATE INDEX idx_t_a_b_desc ON t (a ASC, b DESC);
See references/examples.md for a full filesort-fix walkthrough.
Missing-Index Detection Pattern
A query is "missing an index" when EXPLAIN reports type=ALL or key=NULL against a table over a meaningful row threshold (say, 10k rows). Detection at workload scale :
-- 10.6+ : queries logged because they did not use an index-- requires log_queries_not_using_indexes = ON-- read directly from slow-log file
Or, via performance_schema :
SELECTLEFT(digest_text, 80) AS query,
count_star AS calls,
sum_no_index_used AS no_idx_count,
sum_rows_examined AS total_rows_scanned
FROM performance_schema.events_statements_summary_by_digest
WHERE sum_no_index_used >0ORDERBY sum_rows_examined DESC
LIMIT 20;
sum_no_index_used is the count of executions where no index was used. ALWAYS check selectivity BEFORE adding an index : an index on a column with 3 distinct values across 10M rows is worthless. See references/anti-patterns.md for the "index every slow column" anti-pattern.
Galera-Aware Slow-Log Handling
On a Galera cluster, every node executes write-set replication for every committed transaction. The slow log on a REPLICA node will show writes that originated on a different node. ALWAYS aggregate slow-log data per-node, then deduplicate by digest across nodes : the same slow write-set will appear on all N nodes.
For read traffic, slow log on a node only reflects reads landing on that node, so per-node aggregation matters for read distribution analysis. See mariadb-impl-galera-cluster for cluster setup ; this skill only covers the slow-log slice.
What This Skill Does NOT Cover
General EXPLAIN semantics deep dive : see mariadb-syntax-explain-and-optimizer.
Index design (when to use covering vs composite vs functional) : see mariadb-impl-indexing.
InnoDB buffer-pool tuning, innodb_io_capacity, per-connection buffer sizing : see mariadb-impl-performance-tuning.
Optimizer trace via optimizer_trace=enabled=on : see mariadb-syntax-explain-and-optimizer.
Deadlock and lock-wait diagnosis : see mariadb-errors-deadlocks.
Reference Links
references/methods.md : complete slow-log variable matrix (defaults, scope, dynamic, version), log_slow_verbosity flag values with version notes, log_slow_filter flag values, performance_schema digest-and-history tables and key columns, pt-query-digest and mariadb-dumpslow CLI options.
references/examples.md : 10+ working examples (enable slow log + verbosity, per-session trace, pt-query-digest run, top-10 from performance_schema, ANALYZE FORMAT=JSON for one query, detect missing index from digest, fix Using filesort with 10.8+ descending index, detect Using temporary on big GROUP BY, log_slow_rate_limit for noisy workloads, mariadb-dumpslow fallback).
references/anti-patterns.md : 8+ real anti-patterns (slow log off in production, long_query_time=10 misses sub-second, trusting deprecated SHOW PROFILE, trusting EXPLAIN rows estimate as actual, indexing every slow column without selectivity, ignoring Using temporary on big GROUP BY, log_output=TABLE in production, forgetting slow-log rotation).
Source Verification
All facts in this skill were verified via WebFetch against :
mariadb.com/kb/en/slow-query-log-overview/ : 10.11+ rename to log_slow_query, log_slow_query_file, log_slow_query_time, log_slow_min_examined_row_limit with old names retained as aliases ; log_slow_admin_statements deprecated from 11.0+ in favour of log_slow_filter ; log_slow_filter flag set includes filesort, filesort_on_disk, tmp_table, tmp_table_on_disk, admin, not_using_index.
mariadb.com/kb/en/explain-in-the-slow-query-log/ : log_slow_verbosityexplain flag introduced in 10.1.0 ; EXPLAIN inline only when log_output = FILE.
mariadb.com/kb/en/analyze-statement/ : ANALYZE FORMAT=JSON semantics ; r_rows and r_filtered actual-observation columns ; warning that ANALYZE UPDATE / DELETE actually mutate.
Vooronderzoek Cluster-2 §9 : EXPLAIN columns, Using filesort and Using temporary as red flags, type ranking system to ALL.
