MySQL Slow Query Log Analyzer

Slow query logs turn a vague database complaint into measured SQL evidence. When a checkout step stalls, a report job runs long, or database CPU rises after a deploy, the log can show which statements crossed the server's slow-query rules and how much work MySQL reported for each one. The useful clues are not only elapsed time. Rows examined, rows sent, lock wait, temporary-table counters, sort counters, timestamps, schemas, and repeated SQL shapes all change how the evidence should be read.

MySQL slow logging is selective. A server records statements according to settings such as long_query_time, row-examination limits, administrative-statement logging, no-index logging, and throttling for no-index queries. A missing query in the slow log does not prove that the query was fast, and one slow entry does not prove that it caused the user-visible incident. It proves that the statement met the logging rules for that server at that time.

The strongest slow-log review usually compares repeated statement patterns instead of chasing the single slowest line. Literal values such as order IDs, status strings, timestamps, and UUIDs often change from call to call while the access path stays the same. Grouping similar SQL shapes makes it easier to see that a common endpoint is spending twenty seconds across ten calls, even when no single call looks as dramatic as a one-off report.

Several terms matter before tuning work begins. Query_time is elapsed statement time reported by MySQL, Lock_time is time spent acquiring locks, Rows_examined is the number of rows the server layer examined, and Rows_sent is the number returned to the client. Extra counters can point toward temporary tables, sorting, handler reads, bytes sent, and statement errors when that extra slow-log detail is enabled.

Slow-log evidence is a starting point, not a final fix list. It does not show the current optimizer choice, buffer-pool state, lock owners, application retries, or whether the slow sample came from normal peak load. Good tuning keeps the log, the execution plan, the real parameter values, and the affected user workflow together.

How to Use This Tool:

Use the analyzer with a MySQL FILE-style slow-log excerpt from the incident window, service, schema, or scheduled job you want to review.

1. Paste entries into Slow query log, drop a LOG/TXT/SLOW file, or use Browse LOG/TXT. The source should keep each # Query_time line with the SQL statement that follows it.
2. Choose Review focus before reading the results. Use total time for workload cost, P95 or max time for tail latency, lock time for waits, rows examined for scan pressure, and sample count for repeated call patterns.
3. Set Slow threshold to the latency gate you want to use. Match long_query_time when you are replaying server policy, or set a tighter gate when the application endpoint has a lower response budget.
4. Set Rows examined warning for the workload. A transactional endpoint usually deserves a lower row gate than a known analytical report.
5. Open Advanced when you need a Database or service label in reports or a different Visible row limit for the tables. The visible limit is constrained from 5 to 100 rows.
6. If Check slow-log source reports no complete entries, copy a smaller source slice with # Query_time metadata and SQL text together. Loaded files over 2 MB are rejected so browser analysis stays responsive.
7. Start with Slow Log Snapshot, then move to Fingerprint Hotspots and Tuning Findings. The result is ready for review when the summary reports parsed entries, the hotspot table has ranked fingerprints, and any Check slow-log source warning has either been resolved or documented as a known parse gap.

Interpreting Results:

The summary is the first sanity check. Slow Log Needs Source means the input did not produce complete entries. Fingerprint Review Queue means at least one grouped SQL shape crossed a configured gate or severity rule. Slow Log Below Gates means the parsed sample stayed under the current latency and scan gates, not that the database is healthy in every workload window.

Fingerprint Hotspots is usually the best place to choose the first review target. Read the table in the order set by Review focus, then compare sample count, total Query_time, P95, total Lock_time, max Rows_examined, signals, and severity. A repeated medium statement can consume more capacity than a single high-latency outlier.

Tuning Findings turns the parsed evidence into next checks: latency gate crossings, row-scan pressure, low row yield, lock waits, temporary or sort counter pressure, SQL pattern signals, and parse gaps. Treat those findings as a triage queue. Confirm the top fingerprints with EXPLAIN, EXPLAIN ANALYZE, representative parameter values, and before-and-after workload measurements before adding indexes or rewriting SQL.

Low severity can still matter when the excerpt is too short, the selected gates are too broad, or the real user workflow has a tighter latency budget. Raise confidence by comparing the slow-log window with application traces, database metrics, and the current execution plan.

Technical Details:

MySQL writes a FILE slow-log statement after the statement has executed and after locks have been released, so log order can differ from execution order. The core timing line records Query_time, Lock_time, Rows_sent, and Rows_examined. With extra slow-log detail enabled, a statement can also carry per-statement counters for thread ID, bytes, handler reads, temporary tables, sorting, and errors.

Rows examined and rows sent answer different questions. A statement that examines 200,000 rows and returns five has a weak row yield even when it completes under the current slow threshold. A statement that examines one row but waits on a lock is a concurrency problem, not a scan problem. A statement with disk temporary tables or merge-sort passes points toward grouping, sorting, memory limits, or index-order review.

Formula Core:

Slow-log triage uses simple aggregates before plan review begins. Total time shows accumulated workload cost, P95 keeps a high-end timing value visible, and row yield shows how much examined row work came back to the client.

For two repeated samples at 2.431 seconds and 2.118 seconds, total query time is 4.549 seconds. The P95 position resolves to the larger sample because the group has only two entries. When Rows_examined is zero, row-yield review should avoid division by zero and treat the scan signal as absent rather than infinite.

Transformation Core:

A practical slow-log review moves from raw blocks to comparable groups. Complete entries are parsed from timing metadata plus SQL text, optional schema hints and timestamps are attached when present, and literal values in SQL are folded so repeated statement shapes can be compared.

Rule Core:

Advanced Tips:

• Change Review focus before comparing screenshots, CSV exports, or the Fingerprint Review Ladder. The same log can name a different first target when the queue is sorted by total time, P95, lock time, rows examined, or sample count.
• Use a Slow threshold that matches the question. Matching long_query_time is useful for server-policy review, while a lower gate is better when a specific application endpoint has a tighter latency budget.
• Lower Rows examined warning for OLTP paths where one request should touch only a small slice of data. Raise it for expected reporting jobs so the findings do not bury real latency or lock signals under planned scans.
• Keep Visible row limit high enough to include the competing fingerprints you may tune together, then export the focused table once the queue is stable. JSON keeps the broader parsed result while visible tables stay capped.
• Use Normalize after pasting rotated-log fragments with inconsistent spacing. It cleans trailing whitespace and repeated blank lines without changing the SQL literals used for fingerprint grouping.
• Before sharing copied rows or exported files, scan the SQL fingerprints and sample SQL for emails, account IDs, hostnames, schemas, or ticket labels that should stay private.

Privacy and Limitations:

Slow logs can contain production SQL, literal values, hostnames, usernames, schema names, ticket context, and business identifiers. The analysis runs in the browser, but changed form values may be reflected in the page URL so the current state can be reopened or shared.

• Do not share a page URL after pasting real slow-log text unless you have confirmed that the URL does not contain sensitive log content.
• Loaded source files are limited to 2 MB. Use an incident slice rather than a full rotated log when the source is large.
• The result does not include current execution plans, buffer-pool state, lock owners, application traces, or production impact by itself.
• Confirm fixes with EXPLAIN, EXPLAIN ANALYZE, realistic parameter values, and before-and-after measurements from the affected workload.

Worked Examples:

Checkout SELECT repeated with different statuses

Two entries show Query_time values of 2.431 and 2.118 seconds, with Rows_examined above 176,000 each time. With Review focus set to total time, Fingerprint Hotspots groups the two statements into one SQL shape and ranks it at 4.549 seconds of total Query_time. Tuning Findings should report latency and row-scan pressure, which makes the next check an execution plan with the same status and date pattern used by the app.

Short elapsed time with poor scan yield

A lookup at 0.284 seconds can sit below a 1.0 second Slow threshold, but Rows_examined: 50,000 and Rows_sent: 1 still create a low row-yield signal. Query Ledger keeps the individual entry visible, while Tuning Findings points toward predicate selectivity and index order instead of pure latency.

Update waiting on a lock

An UPDATE with Rows_examined: 1 and Lock_time: 0.128 is not a read-scan problem. When Review focus is lock time, Fingerprint Hotspots can move that statement higher in the queue. The useful follow-up is transaction and hot-row review, not a new read index.

Copied source missing SQL text

If a pasted fragment includes # Query_time lines but omits the following SQL statements, Check slow-log source reports incomplete blocks and Tuning Findings records parse coverage gaps. Copy the full block from the slow log so the timing metadata, optional SET timestamp, and SQL text stay together.

FAQ:

Glossary:

Query_time

The elapsed statement time reported by MySQL for a slow-log entry, in seconds.

Lock_time

The reported time spent acquiring locks before the statement could proceed.

Rows_examined

The number of rows the MySQL server layer examined while executing the statement.

Rows_sent

The number of rows returned to the client.

SQL fingerprint

A normalized SQL shape with literal values collapsed so repeated statements can be grouped.

Row yield

The ratio of rows returned to rows examined, useful for spotting broad scans that return little data.

References:

• The Slow Query Log, MySQL 8.4 Reference Manual.
• mysqldumpslow - Summarize Slow Query Log Files, MySQL 8.4 Reference Manual.
• Optimizing Queries with EXPLAIN, MySQL 8.4 Reference Manual.
• EXPLAIN Statement, MySQL 8.4 Reference Manual.
• How to analyze MySQL or MariaDB slow query log, Simplified Guide.
• How to use EXPLAIN to view query plans in MySQL or MariaDB, Simplified Guide.