A slow-query incident is rarely a single bad SELECT. In a high-traffic MySQL application, one query pattern can consume connection slots, extend lock waits, delay replication, and turn a local regression into an application-wide outage.

This is an incident-response playbook, not an index checklist. Use it to identify which failure mode you have, contain impact, prove the root cause, and prevent the same query shape from returning. For an independent review of query plans, configuration, and operational risk, start with a Mydbops performance and security audit.

TL;DR

• Do not start by adding an index: first classify the incident as execution, queueing, lock contention, or replica lag.
• Rank query digests by total database time and rows examined, then validate the selected plan with EXPLAIN before changing schema.
• Use EXPLAIN ANALYZE only where executing the statement is safe; it runs the query and reports actual iterator timing.
• Contain incidents with connection-pool limits, query-specific guardrails, and a clear kill policy before making permanent changes.
• Prevent recurrence with query-shape alerting, release checks, and an owner for the top database-time consumers.

Start with the incident signature

Before opening the slow query log, establish what users are actually experiencing. Pull a 15-minute window around the alert and line up these four signals:

• application request latency and error rate
• Threads_connected, connection-pool wait time, and aborted connections
• CPU, disk latency, and InnoDB buffer-pool pressure
• database query latency, lock waits, and replication lag

This separates four problems that are often all labelled “slow queries.”

Do not treat these as interchangeable. Raising max_connections does not fix an unselective query. Adding an index does not resolve a transaction holding a lock for 40 seconds.

First 30 minutes: contain the blast radius

1. Protect the primary from further queueing

If connection demand is climbing, cap the application pool before increasing the database ceiling. A pool that opens hundreds of connections per application instance can push MySQL into context switching and memory pressure while hiding the real query problem.

Record the peak Threads_connected, the configured max_connections, and pool wait time. If the pool is saturated but database CPU is low, the bottleneck may be a small pool or a long transaction. If pool wait time and CPU are both rising, reduce parallel work and identify the database-time leader before admitting more traffic.

2. Freeze risky changes

Pause schema migrations, batch jobs, report exports, and backfills that overlap the incident window. Do not add an index or change an execution plan under peak load unless you have assessed the lock and resource impact for that exact MySQL version and table.

If a query must be terminated, capture its normalized digest, application endpoint, connection ID, transaction age, and estimated business impact first. Killing the same query repeatedly without preserving evidence only postpones the next incident.

3. Name an escalation threshold

Set a practical operational rule: if the customer-facing latency SLO is breached for a sustained interval, or connection wait time continues to increase after traffic is reduced, assign a database owner and move from observation to mitigation. Teams without 24/7 coverage should define this hand-off in advance; remote DBA services can provide the operational coverage and root-cause workflow for that gap.

Diagnose from query shape, not query count

The slow query log records statements that exceed long_query_time and meet the configured row-examination threshold. It is useful, but it is not the whole picture: lock acquisition time is reported separately, and a high-frequency query can dominate total database time even when each execution is relatively fast.

Rank the workload by all of the following:

1. total execution time across the incident window
2. execution count
3. average and worst-case latency
4. rows examined compared with rows returned
5. lock time and transaction age

Use pt-query-digest or Performance Schema digest summaries to group parameterized versions of the same statement. The question is not “Which query was slow?” It is “Which query pattern consumed the most database capacity while users were affected?”

Build an evidence packet for each candidate

For the top two or three digests, capture:

• the normalized SQL and example bind values
• schema definition and existing indexes
• EXPLAIN FORMAT=TREE or EXPLAIN FORMAT=JSON
• actual row count and data distribution for filter columns
• execution count, rows examined, and p95 latency before the incident
• application endpoint, release ID, and tenant or workload class

This evidence packet prevents two common errors: indexing a query that only became slow because of lock waits, and tuning a query plan that only appears during an unbounded report request.

Read the plan before changing the schema

EXPLAIN shows the optimizer’s intended execution plan. In MySQL 8.4, EXPLAIN ANALYZE executes the statement and reports iterator-level timing, row counts, and loops. That makes it valuable for comparing estimates with reality, but it also means you should run it only where the statement is safe to execute, typically a production-scale replica or a bounded SELECT.

Look for the mismatch, not a single scary label:

• Large rows examined, few rows returned: assess a selective index or a rewritten predicate.
• Estimated rows far from actual rows: refresh statistics and inspect skewed values before forcing an index.
• Repeated nested-loop work: reduce fan-out, eliminate N+1 application calls, or pre-aggregate where appropriate.
• Temporary tables or sorting at high volume: inspect the ORDER BY, grouping strategy, and available composite indexes.
• A sensible plan but high elapsed time: investigate I/O, lock waits, or competing workload rather than adding an index.

Composite indexes should follow the predicates and access path the query actually uses, not a generic “put every filtered column in an index” rule. Verify the chosen index after the change and measure its write cost; every additional index must be maintained on inserts and updates.

Choose the remediation that matches the failure mode

When the plan is wrong or unselective

Fix the query or index in a staging environment that reflects production cardinality. Test the exact parameter range that triggered the incident, not just a convenient sample. For large production tables, validate the online DDL path, metadata-lock exposure, rollback plan, and replication impact before scheduling the change.

A good remediation has a measurable contract: reduced rows examined, lower p95 execution time, and no unacceptable regression to write latency.

When the application is generating too much work

N+1 patterns, unbounded exports, and repeated existence checks are application design problems. Group work into set-based queries, paginate or cap report endpoints, cache stable read paths, and make heavy operations asynchronous where the business flow allows it.

Do not mask this with a larger database instance. If the workload is multiplying with every request, a temporary hardware gain will be consumed by the next traffic increase.

When reads are crowding out writes

Read replicas, caching, and database-aware routing can isolate non-critical reads from the transactional primary. The rule is simple: only route a read after defining its consistency requirement and observing replica lag under the same load pattern.

ProxySQL configuration and deployment is relevant when connection multiplexing, query routing, or read/write splitting solves the diagnosed constraint. It is not a substitute for fixing a query that performs a full scan on the primary.

When contention is the real issue

A query can appear slow because it is waiting, not because it is executing badly. Inspect transaction age, lock waits, and the code path that starts the transaction. Shorten the transaction, remove user interaction from inside it, split broad updates into safe batches, and make lock order consistent across competing flows.

The target is not merely a lower query time. It is a smaller lock-hold window and predictable recovery when the workload is under pressure.

Turn the fix into a prevention system

The article template should not end with “set up alerting.” Define the controls that would have caught this exact incident earlier:

• Digest-level alert: alert when a known query shape exceeds its baseline for total database time, rows examined, or p95 latency.
• Pool alert: alert on sustained pool wait time and connection utilization, not only on database CPU.
• Lock alert: alert on transaction age and lock-wait growth for write-critical tables.
• Release gate: require an EXPLAIN review for new high-volume query paths, migrations, and reporting endpoints.
• Weekly review: assign an owner to the top database-time digests and record whether each is expected, optimized, or scheduled for removal.

For ProxySQL-based observability, Monitoring MySQL using ProxySQL shows how query digests, backend availability, replication lag, and connection activity can be inspected from the proxy layer.

A practical post-incident review

Close the incident only when the team can answer these six questions:

1. Which normalized query shape or transaction caused the user impact?
2. Was the root cause execution, queueing, locking, replica lag, or infrastructure pressure?
3. What evidence proved that diagnosis?
4. What change reduced the constraint, and what was its measured effect?
5. What alert or release control would have detected it earlier?
6. Who owns the follow-up work and when will it be validated under representative load?

This turns a one-off optimization into durable operational knowledge. It also creates a useful audit trail for regulated workloads; teams handling cardholder data should pair performance investigation with the controls described in this PCI-DSS database audit guide.

FAQ

What should I check first when MySQL queries suddenly become slow?

Classify the incident first: compare application latency, pool wait time, database execution time, lock waits, and replica lag over the same window. This separates plan regressions from connection queueing and contention.

Is EXPLAIN ANALYZE safe to run in production?

EXPLAIN ANALYZE executes the statement to collect actual timing and row information. Use it only for statements that are safe to run, preferably on a production-scale replica or a bounded read query.

Why can a query with low average latency still cause an outage?

A high-frequency query can consume the most total database time even if each execution is short. Rank digests by total execution time, execution count, rows examined, and tail latency rather than average time alone.

Should I increase max_connections during a slow-query incident?

Not as a first response. More connections can add memory and scheduling pressure while the underlying query, lock, or pool configuration remains unresolved. Measure pool wait time and connection utilization before changing the ceiling.

When should I use ProxySQL for a MySQL performance problem?

Use it when the diagnosed constraint involves connection management, read/write routing, query caching, or replica-aware traffic distribution. It does not fix an unselective query or a long-held transaction by itself.

How do I know an index change actually fixed the incident?

Compare the same query shape before and after the change: chosen plan, rows examined, p95 execution time, total database time, and write latency. Validate with the parameter range that caused the incident.

Related guides

• How to prepare a database for a PCI-DSS compliance audit

Need an incident-ready MySQL DBA team?

Mydbops provides 24/7 DBA coverage, query-plan analysis, and performance remediation for production MySQL environments.