MySQL Slow Query Log Analyzer

A slow database incident rarely comes from one spectacular statement. A query that takes 300 ms but runs thousands of times can consume more capacity than a five-second report run once. MySQL slow-query logs make those costs visible by pairing SQL text with elapsed time, lock time, rows sent, rows examined, and optional sorting or temporary-table counters.

Log contents depend on server policy. MySQL normally considers long_query_time, minimum examined rows, administrative-statement settings, no-index logging, and throttling before recording a statement. The entry is written after execution and after locks are released, so file order can differ from start order. Absence from the log does not prove a query was fast.

Four measurements answer different tuning questions:

• Query_time is the statement's reported execution time in seconds.
• Lock_time records time spent acquiring locks, not every form of waiting or contention.
• Rows_examined counts rows examined by the server layer, while Rows_sent counts rows returned to the client.
• Temporary-table and sort counters point toward extra work but do not identify the correct index by themselves.

Repeated SQL often differs only in literal values. Replacing strings, numbers, UUIDs, and hexadecimal values with placeholders creates a fingerprint that can be grouped across calls. This shows accumulated time, recurrence, tail latency, and scan pressure for a query shape without treating every customer or order ID as a separate statement.

Fingerprints also have limits. Different literals can produce different optimizer plans because data distribution and selectivity change. Two statements with the same simplified shape may use different schemas, indexes, bind types, or execution contexts. Keep representative values and the original incident window available for plan review.

Slow-log analysis prioritizes investigation; it does not prescribe an index. Confirm high-cost fingerprints with current statistics, EXPLAIN or EXPLAIN ANALYZE, transaction context, workload concurrency, and before-and-after measurements.

How to Use This Tool:

Use a MySQL FILE-style excerpt that keeps each # Query_time line with the SQL statement that follows it.

1. Paste or load the Slow query log from one incident, service, or comparison window. Incomplete blocks without both timing metadata and SQL are excluded.
2. Choose Review focus. Total time highlights accumulated cost; P95 or max time highlights latency; lock time, rows examined, and sample count highlight different pressure.
3. Set the Slow threshold from 0 to 86,400 seconds and the Rows examined warning from 0 to one trillion rows. Equality triggers both gates.
4. Add a Database or service label only when it helps identify the report, and set the visible ledger limit from 5 to 100 rows.
5. Read Fingerprint hotspots in the selected ranking order, then inspect Query ledger and Tuning findings for the evidence behind each signal.
6. Take the original SQL and representative literals into a current execution-plan review before changing indexes, schema, or query text.

Interpreting Results:

The first fingerprint changes with Review focus. A query can rank first by total time without having the largest single duration, or rank first by rows examined while contributing little elapsed time. Keep the selected metric visible when sharing a ranked result.

A fail fingerprint has an extreme latency, extreme scan, or disk temporary-table signal. Review means one or more other signals were found. Pass means no local signal matched in the supplied excerpt; it does not certify the query or database.

Parse gaps weaken totals and percentiles because incomplete blocks are excluded. A short sample can also make nearest-rank P95 equal the maximum. Compare the parsed-entry count and time window before treating the ordering as stable.

Technical Details:

FILE slow-log blocks are split around timing and date markers. A usable block needs a finite Query_time and SQL text beginning with a recognized SQL command. Comments, timestamp setup statements, and USE lines are excluded from the SQL text before fingerprinting.

Transformation Core:

Formula Core:

Total time measures accumulated cost. Row yield helps identify scans that return a small fraction of examined rows. P95 uses a nearest-rank rule rather than interpolation.

x is the ascending list of query times, indexed here from 1 for readability. With two samples, nearest-rank P95 selects the larger value. Low row yield is flagged only when at least 1,000 rows were examined and the ratio is below 0.01; a zero examined-row count does not create an infinite ratio.

Rule Core:

Input is limited to 2 MiB and the first 10,000 split blocks. Threshold values of zero are valid; they make every non-negative entry meet the basic latency or scan signal, while the four-times and twenty-times extreme rules remain disabled because those require a positive threshold.

Privacy and Limitations:

Slow-log text is processed in the browser and excluded from page URL state. The original SQL can contain customer identifiers, email addresses, tokens, tenant names, comments, or business-sensitive predicates. Fingerprints mask common literals but are not a general-purpose redaction guarantee.

• Only MySQL FILE-style blocks are parsed; rows copied from the mysql.slow_log table need conversion.
• The analysis does not know schema statistics, index definitions, server variables, transaction owners, buffer state, or concurrent workload.
• SQL-pattern signals are text heuristics. For example, SELECT * or a JSON predicate can be acceptable for a specific schema and workload.
• Changing thresholds changes findings and states, so keep them fixed for before-and-after comparisons.

Worked Examples:

References:

• The Slow Query Log, 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.