Promptlyb

SQL Query Optimizer

LW
@Lisa Wang
• 4 days ago
Fork

Analyze and optimize the following SQL query for performance.

Database Context

  • Database Engine: {{db_engine}}
  • Table Sizes: {{table_sizes}}
  • Current Performance: {{current_performance}}

Query to Optimize

{{query}}

Analysis Required

1. Query Explanation

  • What does this query do?
  • Step-by-step execution plan

2. Performance Bottlenecks

Identify issues:

  • Full table scans
  • Missing indexes
  • Inefficient JOINs
  • Unnecessary subqueries
  • N+1 patterns
  • Cartesian products

3. Optimized Query

Rewrite the query with:

  • Proper indexing hints
  • Optimized JOIN order
  • Reduced data scanning
  • Efficient filtering
-- Optimized version

4. Index Recommendations

CREATE INDEX ...

For each index:

  • What it optimizes
  • Space/write tradeoff
  • When to use partial indexes

5. Explain Plan Comparison

Show expected improvement:

  • Before: [estimated rows, cost]
  • After: [estimated rows, cost]

6. Additional Recommendations

  • Query rewriting techniques
  • Denormalization options
  • Caching strategies
  • Partitioning suggestions

Variables 4

Database Engine {{db_engine}}
e.g., PostgreSQL 15, MySQL 8, SQL Server
Table Sizes {{table_sizes}}
e.g., users: 1M rows, orders: 10M rows, order_items: 50M rows
Current Performance {{current_performance}}
e.g., Takes 30 seconds, times out on production
SQL Query {{query}}
SELECT * FROM ...