SQL Query Optimizer

The Prompt

Analyze this SQL query for performance issues. Provide:

1. **Execution Analysis:**
   - Estimated complexity (full table scan? index seek?)
   - Which joins are likely expensive and why
   - Subquery vs JOIN opportunities

2. **Optimization Plan:**
   - Rewritten query (if improvement possible)
   - Suggested indexes (with CREATE INDEX statements)
   - Partitioning recommendations (if table is large)

3. **Trade-offs:**
   - Read vs write performance impact of suggested indexes
   - Storage cost of new indexes
   - When to denormalize vs keep normalized

Database engine: [PostgreSQL/MySQL/SQLite]
Table sizes: [approximate row counts if known]

Query:
[PASTE SQL]

How To Use It

Paste your slow query along with table definitions (CREATE TABLE statements) if available. Mentioning approximate table sizes helps the AI reason about whether a full scan or index scan is appropriate.

Why It Works

Query optimization requires understanding both the logical intent and physical execution. By asking for trade-offs, this prompt prevents the naive “add an index on everything” advice and produces recommendations that consider write amplification.

Variations

Explain execution plan:

Translate this EXPLAIN ANALYZE output into plain English. What's the bottleneck and why?

Schema design review:

Review this database schema for: normalization issues, missing constraints, index gaps, and scalability concerns at 10M+ rows.

Query rewrite for readability:

Rewrite this query to be more readable (CTEs, meaningful aliases) without changing its behavior or performance characteristics.