Back to prompts

CodingChatGPTClaude

SQL Query Optimizer

Analyze and rewrite slow SQL queries for better performance with explanations of every optimization made.

Prompt Template

You are a database performance expert. Analyze and optimize the following SQL query:

Database: [e.g., PostgreSQL, MySQL, SQLite]
Query:
```sql
[paste your query here]
```

Table schemas (if relevant):
[paste CREATE TABLE statements or describe columns]

Current problem: [e.g., takes 8 seconds on 2M rows, causes timeouts]
Existing indexes: [list them if known]

Provide:
1. Analysis of why the query is slow
2. Optimized query (with comments)
3. Recommended indexes to add
4. Explanation of each change made
5. Estimated improvement (rough %)

Example Output

Analysis

The query performs a full table scan on `orders` (2M rows) because:

- `WHERE status = 'pending'` filters on an unindexed column

- The `JOIN` on `users.email` uses a non-indexed string column

- `SELECT *` fetches unnecessary columns, increasing I/O

Optimized Query

SELECT o.id, o.total, o.created_at, u.name

FROM orders o

INNER JOIN users u ON u.id = o.user_id -- join on indexed integer FK

WHERE o.status = 'pending'

AND o.created_at > NOW() - INTERVAL '30 days'

ORDER BY o.created_at DESC

LIMIT 100;

Recommended Indexes

CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

Changes Made

- Replaced `JOIN ON email` with `JOIN ON user_id` (integer FK, much faster)

- Added date range filter to reduce result set before sorting

- Replaced `SELECT *` with specific columns

**Estimated improvement:** 15x–40x faster with the composite index.

Tips for Best Results

💡Always run EXPLAIN ANALYZE before and after to measure actual improvement
💡Composite indexes should have the most selective column first
💡Never optimize prematurely — profile first, then fix the actual bottleneck

Try it with

Related Prompts

Code Review Assistant

Get a thorough, senior-level code review with actionable feedback on quality, security, performance, and best practices.

ChatGPTClaudeGemini

Debugging Detective

Systematically debug errors and unexpected behavior with root cause analysis and fix suggestions.

ChatGPTClaudeGemini

Code Refactoring Advisor

Transform messy, complex code into clean, maintainable, well-structured code with clear explanations.

ChatGPTClaudeGemini