How to debug a slow SQL query

1. Reproduce the problem

Record the exact query shape, representative parameter values, approximate table sizes, when the slowdown occurs, and a baseline latency distribution. Redact credentials and sensitive values while preserving types and data shape. A query that is slow only under concurrency may not be diagnosed by a quiet single-user test.

2. Collect safe plan evidence

Begin with EXPLAIN, which reports optimizer plan information without executing the described SELECT in the ordinary way:

EXPLAIN
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Capture the complete output, not only one column. Look for scans, selected indexes, estimated row counts, join order, temporary work, and sorting. An estimated full scan is not automatically wrong when much of a table is needed.

3. Add runtime evidence carefully

MySQL 8.0.18+ supports EXPLAIN ANALYZE for supported statements and executes the query to collect actual iterator statistics. MariaDB's ANALYZE SELECT also executes the SELECT and adds actual statistics. Use these only in a suitable environment after considering cost and data sensitivity.

Official references: MySQL EXPLAIN and MariaDB ANALYZE and EXPLAIN.

4. Find the expensive work

Compare estimated rows with actual rows when available. Find where row counts multiply, filters discard many rows late, or a sort/materialization dominates. Check whether statistics are current and whether parameters have skewed distributions. Read the entire plan because the visible slow symptom can originate earlier in a join or filter.

5. Test one change

Possible hypotheses include a composite index aligned with equality predicates and ordering, a more selective predicate, or a query rewrite. Review existing indexes and workload first. Indexes cost storage and can slow writes; rewrites can change NULL behavior, duplicate handling, or result ordering. Do not apply generic advice without confirming semantics.

6. Verify correctness and performance

1. Run old and new forms against representative data and compare result sets.
2. Repeat timing tests under comparable cache and concurrency conditions.
3. Compare median and tail latency, rows read, plan shape, and resource use.
4. Check write impact and monitor after controlled rollout.

One faster run does not establish a reliable improvement. Keep a rollback path for production changes.

Minimum evidence checklist

• Engine and exact version
• Query text and representative parameter shapes
• Table definitions and index definitions
• Complete EXPLAIN output; runtime plan only when safe
• Baseline and candidate measurements, with test conditions

TuningLens beta guides evidence collection for MySQL and MariaDB SELECT investigations. It does not connect to your database or execute SQL; you control the diagnostics and review any recommendation.