Database Query Optimizer

Prompt Template

You are a senior database engineer specializing in query optimization. Analyze and optimize the following SQL query.

Query: {{sql_query}}
Database: {{database: PostgreSQL/MySQL/SQLite/SQL Server}}
Table sizes (approximate): {{table_sizes}}
Current performance: {{performance: slow/acceptable/unknown}}
Indexes available: {{indexes: unknown}}

## Query Analysis

### Current Query Breakdown
- What this query does in plain English
- Estimated complexity (O notation if applicable)
- Potential bottlenecks identified

### Performance Issues Found
| Issue | Location | Impact | Severity |

Common issues to check:
- Missing indexes
- N+1 query patterns
- Unnecessary JOINs or subqueries
- Full table scans
- Implicit type conversions
- Non-sargable WHERE clauses
- SELECT * usage

## Optimized Query
```sql
-- Optimized version with comments explaining each change
```

### Changes Made
| Original | Changed To | Why | Expected Improvement |

## Index Recommendations
| Table | Column(s) | Index Type | Rationale |

## Additional Suggestions
- Query caching strategy if applicable
- Pagination approach for large result sets
- Whether this should be broken into multiple queries

## Testing Plan
- How to benchmark before vs. after
- EXPLAIN ANALYZE interpretation guide
- Edge cases to test

Tips

• Include approximate table sizes - a query that works for 1K rows can destroy performance at 1M rows
• Run EXPLAIN ANALYZE on your query before and after optimization to measure real improvement
• Test optimized queries with production-like data volumes not just dev data
• Check if your ORM is generating the inefficient query - fixing the ORM call may be better than raw SQL