MySQL Slow Query Log + EXPLAIN Tuning Prompt
Triage a slow MySQL/MariaDB query from the slow query log and produce a step-by-step EXPLAIN-driven tuning plan.
- Target user
- Backend engineers and DBAs chasing a slow query in production
- Difficulty
- Intermediate
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior MySQL/MariaDB DBA who tunes queries for a living. You read EXPLAIN and EXPLAIN ANALYZE output fluently, understand the optimizer's access types (const, eq_ref, ref, range, index, ALL), and know when a query is doing a full table scan, a filesort, or a temporary table. I will provide: - The offending query: [PASTE QUERY] - The slow query log entry (Query_time, Lock_time, Rows_examined, Rows_sent): [PASTE SLOW LOG ENTRY] - `EXPLAIN FORMAT=JSON` and/or `EXPLAIN ANALYZE` output: [PASTE EXPLAIN] - Relevant `SHOW CREATE TABLE` output: [PASTE SCHEMA] - MySQL/MariaDB version and approximate table row counts: [PASTE] Work through this: 1. **Read the EXPLAIN.** Identify the join order, access type per table, `key` used vs `possible_keys`, `rows`/`filtered` estimates, and any `Using filesort`, `Using temporary`, or `Using where` flags. Flag the single most expensive step. 2. **Compare Rows_examined vs Rows_sent** from the slow log to quantify wasted work. A large ratio usually means a missing or unusable index, or a non-sargable predicate. 3. **Find non-sargable predicates** (functions on indexed columns, leading wildcards, implicit type conversion, mismatched collation) that prevent index use. 4. **Propose index or query changes.** Prefer adding/adjusting a composite index over rewriting the query when both work. Explain column order using equality-then-range reasoning. Note any covering-index opportunity. 5. **Predict the new plan** and what EXPLAIN should look like afterward, so I can confirm the fix worked rather than trust it blindly. Output as: (a) Diagnosis, (b) Ranked fixes with the exact DDL/SQL, (c) How to verify each on a replica, (d) Risks. Guardrails: test every index and rewrite against a production-sized replica or staging copy first; back up before any schema change; add indexes on large tables with gh-ost or pt-online-schema-change rather than a blocking ALTER; never run a destructive change on prod without a second reviewer.
Why this prompt works
Slow-query work fails most often because people guess at the cause instead of reading what MySQL is actually doing. This prompt forces the model into the DBA’s real loop: start from the slow query log entry, line it up against EXPLAIN, and find the one step where the optimizer is examining far more rows than it returns. By demanding the Rows_examined-to-Rows_sent ratio and the specific access type, it anchors the analysis in evidence rather than folklore like “add an index and hope.”
It also encodes the judgement calls that separate a safe fix from a risky one. Asking for the predicted post-fix plan means you get a falsifiable claim you can check with EXPLAIN, not a vague promise. Insisting on sargability analysis catches the classic traps — WHERE DATE(created_at) = ..., leading LIKE '%foo', and utf8mb4-vs-latin1 collation mismatches that silently disable an index.
Finally, the guardrails keep a human in control. Index changes on large InnoDB tables can lock writes for minutes if run as a naive ALTER, so the prompt steers toward online schema-change tooling and replica testing. The output format makes the recommendation reviewable, which is exactly what you want before touching a busy production database.