Use MySQL’s slow query log to capture statements that exceed a deliberate execution-time threshold, then use mysqldumpslow and Performance Schema digests to rank recurring query shapes. Validate the leading candidates with EXPLAIN; use EXPLAIN ANALYZE only when executing the statement in your test or production environment is safe.
Need a server to try this on? Antihost offers Linux VPS plans with NVMe storage, full root access and a 14-day free trial.
What MySQL considers a slow query
The slow query log is disabled by default. MySQL writes a statement when it satisfies the configured slow-log conditions, including long_query_time and the examined-row limit. A statement can therefore be slow to a user for reasons that are not captured by a single threshold, such as lock contention, queueing, or an overloaded host.
As an Amazon Associate I earn from qualifying purchases.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsThe default threshold
In the current MySQL 8.0 Reference Manual, long_query_time defaults to 10 seconds. It accepts fractional seconds with microsecond precision and is dynamic at both global and session scope. Lowering it captures more candidates but increases disk usage and logging overhead, so very low values should normally be used for a short, bounded diagnostic window.
Execution time is not lock time
Each slow-log entry separates the statement’s execution time (Query_time) from time waiting for locks (Lock_time). It also records Rows_sent and Rows_examined. Those fields let you distinguish a query that does a large amount of work from one that mostly waits for another transaction.
#1 Best Overall
Enable a controlled capture window
- Inspect the current settings.
SHOW VARIABLES WHERE Variable_name IN (
'slow_query_log',
'slow_query_log_file',
'log_output',
'long_query_time',
'min_examined_row_limit',
'log_queries_not_using_indexes'
); - Select a destination.
log_outputacceptsFILE,TABLE, or both. For a file destination, setslow_query_log_fileto the path monitored by your operating system and log-collection tools. The table destination ismysql.slow_log. - Set a threshold for the capture window.
SET GLOBAL long_query_time = 2.000000;The value above is an example of a temporary diagnostic threshold, not a universal recommendation. Choose a value that is low enough to expose the workload you are investigating and high enough to avoid filling the destination.
- Turn on the output and logger.
SET GLOBAL log_output = 'FILE';
SET GLOBAL slow_query_log = ON;Use
SET GLOBAL log_output = 'FILE,TABLE'if you need both destinations. Logging requires the slow-log switch and an active output destination.Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. - Generate or wait for representative traffic. Keep the interval long enough to include the normal workload, but short enough that the increased log volume is manageable. Record the exact start and end time so you can isolate the relevant entries.
- Restore normal settings when finished.
SET GLOBAL slow_query_log = OFF;
SET GLOBAL long_query_time = 10;Use the threshold that matches your normal policy rather than assuming the 10-second default is appropriate for every application.
Persisting settings after a restart
SET GLOBAL changes the running server. To make a setting survive a restart, place the corresponding options in the MySQL server configuration used by your installation, for example:
[mysqld]
slow_query_log=ON
log_output=FILE
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=10After restarting, verify the effective values with SHOW VARIABLES. Configuration-file locations and service names differ by operating system and package, so do not assume the example path exists on your host.
Choose the output deliberately
FILE output preserves a conventional log stream that can be copied, archived, and processed with mysqldumpslow. TABLE output writes rows to mysql.slow_log, which is convenient when SQL access is preferable. Selecting both gives you the event records in each destination, but also means both destinations receive the additional write activity. Whichever destination you choose, verify it during the capture window instead of assuming that enabling slow_query_log alone is sufficient.
Thresholds, row limits, and session scope
long_query_time is a threshold for eligibility, not a promise that every user-visible slow request will appear. The configured examined-row condition also matters; inspect min_examined_row_limit when a test statement unexpectedly produces no entry. Because the variable is dynamic at global and session scope, a session-level value can differ from the global value. Check the session that runs your reproduction as well as the server-wide setting.
Rank #2
Should you log statements that do not use indexes?
log_queries_not_using_indexes is off by default. Enabling it can reveal table scans and other statements that deserve an access-path review, but it can also produce a very large log on reporting, import, or maintenance workloads.
SET GLOBAL log_queries_not_using_indexes = ON;Pair it with log_throttle_queries_not_using_indexes when the stream is too noisy. A full index scan can still be logged: using an index does not necessarily mean that the index limits the number of rows read. Treat this option as a targeted investigation switch, not a permanent substitute for the normal time threshold.
Read the evidence in each slow-log entry
Start with four fields:
Query_time: total statement execution time in seconds.Lock_time: time spent waiting for locks.Rows_sent: rows returned to the client.Rows_examined: rows MySQL had to inspect.
A high Rows_examined value compared with Rows_sent often points to a selective-index, join, or filtering problem. A large Lock_time with modest row counts points toward contention rather than inefficient row processing. Compare these values across several occurrences instead of diagnosing from one outlier.
Add extended fields when the basic entry is not enough
MySQL 8.0.14 introduced log_slow_extra. Enable it during a diagnostic window when you need thread identifiers, error codes, byte counts, handler counters, sort counters, temporary-table counts, or start and end timestamps:
SET GLOBAL log_slow_extra = ON;These counters help connect a slow statement to a connection, an error pattern, an on-disk temporary table, or a sort operation. Disable the option when the additional volume is no longer useful.
Separate frequency from severity
A single event with a large Query_time may be important, but a moderately slow statement executed thousands of times can consume more total capacity. Read Query_time together with execution count from the grouped reports or Performance Schema. Likewise, a large Rows_examined value is not automatically a problem if the statement is an intentional batch operation; compare it with the statement’s normal frequency and result size.
Group recurring statements with mysqldumpslow
Individual slow-log lines contain literal values, so the same query shape can appear as hundreds of apparently different statements. mysqldumpslow abstracts numbers and strings and groups similar statements before ranking them.
Recommended Free Tools
mysqldumpslow -s t -t 20 /var/log/mysql/mysql-slow.logThis example shows the top 20 groups sorted by query time. Common sorting choices include:
-s tfor total query time.-s atfor average query time.-s cfor execution count.-s lor-s alfor total or average lock time.-s ror-s arfor total or average rows sent.
Use -r to reverse the order, -g pattern to include only statements matching a pattern, and -a when you need literal values rather than abstracted forms. Rank the same file in more than one way: the query with the greatest total time may be frequent, while the query with the worst average may be an infrequent but severe outlier.
Keep a representative statement
After ranking, retain one real statement for each important group. The abstracted form is ideal for prioritization, but validation requires the actual tables, predicates, joins, and parameter values that produced the plan.
Use a bounded log slice
Process only the file or time range that corresponds to your capture window. Mixing old and new entries can make a once-common statement appear current and can hide the effect of a change. If you run several diagnostic windows, label each output with its threshold and start/end time so rankings remain comparable.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Find live query shapes with Performance Schema
Performance Schema normalizes SQL into a digest and aggregates statements by schema and digest in events_statements_summary_by_digest. Normalization allows similar statements to be grouped and summarized, exposing both the types of statements being executed and how often they occur.
SELECT
SCHEMA_NAME,
DIGEST_TEXT,
COUNT_STAR,
ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_seconds,
ROUND(AVG_TIMER_WAIT / 1000000000000, 6) AS average_seconds,
ROUND(MAX_TIMER_WAIT / 1000000000000, 3) AS max_seconds,
SUM_ROWS_EXAMINED,
SUM_ROWS_SENT,
SUM_NO_INDEX_USED,
SUM_NO_GOOD_INDEX_USED,
FIRST_SEEN,
LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;Performance Schema timers are expressed in picoseconds; dividing by 1,000,000,000,000 converts them to seconds. The result ranks cumulative time, which is useful for finding workload-wide cost. To find tail latency, inspect the percentile columns exposed by your MySQL 8.0 installation, such as QUANTILE_95, QUANTILE_99, and QUANTILE_999, when present.
Columns that answer different questions
COUNT_STAR: how often the digest ran.SUM_TIMER_WAIT: total time consumed by all executions.AVG_TIMER_WAITandMAX_TIMER_WAIT: average and worst observed duration.SUM_ROWS_EXAMINEDandSUM_ROWS_SENT: work performed versus result size.SUM_NO_INDEX_USEDandSUM_NO_GOOD_INDEX_USED: executions flagged for index-use concerns.FIRST_SEENandLAST_SEEN: when the digest appeared in the summary.QUERY_SAMPLE_TEXT: a representative statement when populated.
Use the digest summary for continuously aggregated workload behavior, then use the slow log when you need individual event records and their exact timestamps.
Read aggregate fields together
Sort by SUM_TIMER_WAIT to find total workload cost, by AVG_TIMER_WAIT or a percentile to find latency, and by COUNT_STAR to find frequent calls. Compare SUM_ROWS_EXAMINED with SUM_ROWS_SENT for evidence of excessive filtering. The no-index counters are signals for review, not proof that an index should be added. A digest can legitimately represent a scan, and the slow-log event plus its representative text supplies the context needed for a plan check.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Validate a suspected query with EXPLAIN
Start with EXPLAIN
The EXPLAIN statement describes how MySQL plans to execute a statement. For a representative query, inspect join order, access type, chosen and possible keys, estimated rows, and filtering:
EXPLAIN FORMAT=TRADITIONAL
SELECT ...;Compare the estimate with the slow-log and Performance Schema observations. A plan that estimates very few rows but examines many in practice can indicate stale statistics, skewed data, or predicates that are less selective than the optimizer expects.
Use EXPLAIN ANALYZE only when execution is safe
Introduced in MySQL 8.0.18, EXPLAIN ANALYZE executes the statement and reports estimated cost and rows alongside actual iterator timing, returned rows, and loop counts:
EXPLAIN ANALYZE
SELECT ...;Because it runs the statement, use a transaction, a staging copy, or a read-only representative query when appropriate. Do not run an unbounded write or a production query with side effects merely to obtain a plan. The actual iterator timings can show where the estimate diverges from reality and which join or scan consumes the time.
Free tools Windows power users keep installed
One-click scans. No signup required.
Use the sys schema for readable diagnostics
The built-in sys schema provides easier-to-read Performance Schema views and diagnostic procedures. It is useful when raw timer columns and digest tables are too cumbersome for routine triage.
sys.ps_trace_statement_digest() can trace one digest over a polling interval and attempts to run EXPLAIN for the longest-running example. Use it to observe a query shape while traffic is active, then validate the captured representative statement independently.
Best Value
Which method should you use?
| Method | Best for | What it preserves or measures | Main caution |
|---|---|---|---|
| Slow query log | Capturing individual events during a defined window | Threshold-qualified statements, execution time, lock time, rows, timestamps, and optional extended counters | Very low thresholds and no-index logging can consume substantial storage |
mysqldumpslow |
Ranking recurring shapes in a slow-log file | Grouped statements, counts, total or average time, lock time, and row-based rankings | Grouping abstracts literals; retain a real statement for validation |
| Performance Schema digest summary | Continuous workload aggregates | Normalized text, execution count, timing aggregates, rows, index-use counters, first/last seen, and available percentiles | Summary data is aggregated rather than a complete event-by-event history |
EXPLAIN |
Inspecting the optimizer’s intended plan | Join order, access paths, indexes, and estimates | It does not provide actual runtime iterator measurements |
EXPLAIN ANALYZE |
Comparing estimates with observed execution | Actual iterator timing, rows returned, and loop counts | It executes the statement and may have side effects |
A repeatable tuning loop
- Capture a bounded window with a threshold appropriate for the workload.
- Rank the slow log by total time, average time, count, lock time, and rows so frequency and severity are both visible.
- Cross-check the leading shapes in
events_statements_summary_by_digest, including percentile fields when available. - Choose one representative statement and inspect it with
EXPLAIN. - Run
EXPLAIN ANALYZEonly in a safe execution context. - Change one factor at a time, such as an index, predicate, join formulation, or transaction behavior.
- Repeat the same representative workload and compare latency, rows examined, rows sent, lock time, execution count, and plan behavior.
This controlled comparison prevents a change that improves one outlier while making the broader workload worse.
Troubleshooting common gaps
The log is empty
- Confirm
slow_query_logis on. - Confirm
log_outputincludesFILEorTABLE. - Check the actual
slow_query_log_filepath or querymysql.slow_log. - Verify that test statements exceed
long_query_timeand any examined-row limit. - Check whether the statement was rejected before parsing; from MySQL 8.0.29, statements that cannot be parsed are not written to the slow query log.
The log grows too quickly
- Raise
long_query_timeafter the diagnostic window. - Turn off
log_queries_not_using_indexesunless scans are the specific investigation target. - Use
log_throttle_queries_not_using_indexesto limit that category. - Disable
log_slow_extrawhen its counters are no longer needed. - Rotate or archive the destination under your normal operating-system policy.
Performance Schema and the slow log disagree
They answer different questions. The slow log preserves threshold-qualified events, while the digest table aggregates normalized shapes over its active summary. Check the capture times, threshold, digest grouping, and whether the statement ran before or after the summary was reset.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Version-specific details
log_slow_extrais available from MySQL 8.0.14.EXPLAIN ANALYZEwas introduced in MySQL 8.0.18.log_slow_replica_statementsis available from MySQL 8.0.26; replica-statement variable naming has changed across 8.0 releases, so verify the variable supported by your exact server version.- From MySQL 8.0.29, statements that cannot be parsed are not written to the slow query log.
Check the server’s exact 8.0 release before copying configuration between environments, particularly for replica logging, extended fields, and percentile columns.
Frequently Asked Questions
How do I enable the MySQL slow query log?
Set an output destination with log_output, choose a file with slow_query_log_file when using FILE, and turn on slow_query_log. Verify all three values with SHOW VARIABLES; the logger is disabled by default.
What should I set long_query_time to?
The MySQL 8.0 default is 10 seconds, but it is not a universal performance target. Lower it temporarily to capture the workload you are diagnosing, using microsecond precision if needed, and raise or restore it when the bounded window ends to control storage and overhead.
Should I enable log_queries_not_using_indexes?
Enable it when scans are the investigation target. It is off by default and can generate a large volume, especially for reporting or maintenance workloads. Use log_throttle_queries_not_using_indexes if the stream is noisy, and remember that a full index scan may still be logged.
How do I find the worst recurring query?
Run mysqldumpslow against the slow-log file and rank by total time, average time, count, lock time, and rows. Then compare the leading shapes with events_statements_summary_by_digest, retaining a real representative statement for plan validation.
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN describes the optimizer’s intended plan and estimates. Introduced in MySQL 8.0.18, EXPLAIN ANALYZE executes the statement and adds actual iterator timing, rows returned, and loop counts, so use it only where execution and side effects are safe.
Why does Performance Schema not match my slow-log count?
The slow log stores individual statements that meet its threshold and row conditions, while Performance Schema summarizes normalized digests over its active interval. Check the time window, threshold, digest grouping, representative text, and whether the summary was reset.
The Bottom Line
Use a bounded slow-log window to preserve concrete events, rank query shapes with mysqldumpslow, and use Performance Schema digests for continuous counts and tail-oriented aggregates. Separate execution time from lock time, validate with EXPLAIN, and run EXPLAIN ANALYZE only when executing the statement is safe.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
