Database Query Optimizer
Diagnoses a slow SQL query and returns an optimized rewrite, index plan, and execution-plan reasoning.
// prompt
You are a **senior database administrator and SQL performance engineer** specializing in {{database_engine}} (e.g., PostgreSQL, MySQL, SQL Server, Oracle). Diagnose and optimize the query below, explaining every change so I learn the reasoning — not just the result.
## Context
- **Database engine & version:** {{database_engine_and_version}}
- **Slow query:**
```sql
[Current Query]
```
- **Relevant schema:** table definitions, row counts, existing indexes — {{schema_and_indexes}}
- **Symptom:** {{performance_problem}} (e.g., 8s runtime, full table scan, lock contention)
- **Goal:** {{target_outcome}} (e.g., sub-200ms, lower I/O, support N concurrent reads)
## How to proceed
1. **Read the plan.** State which EXPLAIN/ANALYZE output you need; infer likely bottlenecks (scans, bad join order, spills, missing/unused indexes, non-sargable predicates, N+1).
2. **Find the root cause.** Identify the single biggest cost driver and why the planner chose it.
3. **Rewrite the query.** Provide a corrected version that is logically equivalent and returns identical results.
4. **Propose indexes.** Give exact `CREATE INDEX` statements with column order rationale; flag any redundant or write-penalizing indexes.
5. **Note schema/config options** only if they materially help (partitioning, data types, statistics, denormalization) — keep these as suggestions.
## Deliver in this format
- **Diagnosis** — root cause in 2-4 sentences.
- **Optimized query** — a single SQL block, commented at the changed lines.
- **Index recommendations** — `CREATE INDEX` statements + one line each on why.
- **Why it's faster** — map each change to the cost it removes; give a rough before/after estimate.
- **Trade-offs & risks** — write overhead, storage, edge cases to verify.
- **Validation step** — the exact command I should run to confirm the improvement.
Constraints: stay strictly within {{database_engine}}'s dialect and supported features. If the provided schema or plan is insufficient to be certain, list the specific information you need before committing to a recommendation. Do not invent table or column names.
Fill in the variables
Example response
🚀 Query Optimization Results
📊 Performance Analysis
Original Query Time: 2.3 seconds
Optimized Query Time: 0.12 seconds
Improvement: 95% faster ⚡
❌ Original Query Issues
-- Problematic query
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= "2023-01-01"
GROUP BY u.id, u.name
ORDER BY order_count DESC;Problems:
- Missing index on orders.created_at
- Inefficient GROUP BY clause
- No query plan optimization
✅ Optimized Solution
-- Step 1: Create strategic indexes
CREATE INDEX idx_orders_created_user ON orders(created_at, user_id);
CREATE INDEX idx_users_active ON users(id) WHERE active = true;
-- Step 2: Optimized query
SELECT
u.name,
COALESCE(o.order_count, 0) as order_count
FROM users u
LEFT JOIN (
SELECT
user_id,
COUNT(*) as order_count
FROM orders
WHERE created_at >= "2023-01-01"
GROUP BY user_id
) o ON u.id = o.user_id
WHERE u.active = true
ORDER BY order_count DESC
LIMIT 100;
-- Alternative: Using window functions (PostgreSQL)
SELECT DISTINCT
u.name,
COUNT(o.id) OVER (PARTITION BY u.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
AND o.created_at >= "2023-01-01"
WHERE u.active = true
ORDER BY order_count DESC;📈 Index Strategy
- Composite index on (created_at, user_id) for date filtering
- Partial index on active users only
- Consider partitioning for very large tables
Related prompts
Programming & Development
Advanced Debugging Assistant
Diagnoses bugs in your code, pinpoints root causes, and delivers fixed code with prevention tips.
Programming & Development
Algorithm Design Expert
Designs, analyzes, and optimizes algorithms with multiple approaches, Big-O complexity analysis, production code, and tests.
Programming & Development
API Development Architect
Designs a production-ready RESTful API with OpenAPI spec, secure auth, validation, and scalable architecture.
Programming & Development
Code Review Expert
Performs a rigorous senior-level code review covering correctness, design, performance, and security with prioritized, actionable fixes.