What this prompt does
This prompt takes one slow [query], your [table_info], and its [current_time], then walks through a disciplined optimization pass. It interprets the EXPLAIN output, recommends specific indexes (which columns, what type, partial or covering), and proposes query rewrites such as turning a subquery into a join or choosing EXISTS over IN. It also hunts for full table scans, cardinality issues, and lock contention, then ties everything to a concrete [target_time].
The structure works because it makes the model justify every change instead of guessing. Reading EXPLAIN and rewriting the query usually beats reaching for caching first, so the prompt only treats [cache_approach] and denormalization as later levers. By demanding the optimized query with a per-change explanation, plus an index migration and rollback plan, it keeps the optimization safe and reviewable rather than a black-box rewrite. It also pushes into the parts people skip under deadline pressure: statistics and cardinality issues that skew the planner, lock contention that only shows up under load, and a monitoring setup so you can track this query's performance over time rather than re-discovering the regression months later.
When to use it
- A dashboard or API endpoint is slow and you have traced it to one specific
[query]. - You can read EXPLAIN output but want a second opinion on index and rewrite choices.
- You want to exhaust query-level fixes before adding
[cache_approach]complexity. - You need an index migration with a matching rollback plan, not just ad-hoc CREATE INDEX commands.
- You suspect full table scans or stale statistics but want them confirmed and explained.
- You need to hit a hard
[target_time]and want each change justified for review.
Example output
Expect a step-by-step breakdown: an EXPLAIN interpretation, a prioritized list of index recommendations with reasoning, one or more rewritten versions of your [query], notes on table scans and cardinality, and a caching discussion scoped to [cache_approach]. It closes with the final optimized query annotated change-by-change, plus a migration to add the new indexes and a rollback to remove them.
Pro tips
- Paste real EXPLAIN output and accurate
[table_info](row counts, existing indexes); the quality of the advice tracks directly with the detail you provide. - Set a concrete
[current_time]and[target_time]so the model can reason about how aggressive the changes need to be. - Resist applying
[cache_approach]first; let the prompt fix the query and indexes, since caching a slow query just hides the problem. - Review every proposed index before adding it, because redundant indexes slow writes and bloat storage.
- Always keep the rollback migration; an index that helps this
[query]can regress others, and you want a clean exit. - Iterate by feeding back the new EXPLAIN after applying changes and asking it to push closer to
[target_time].
