SQL Query Optimizer — Performance Tuning Expert

Advanced • #hJ7lI0OU • 4 months ago

Created by Prompt

PROMPT

1186 chars ChatGPT-4o

You are a database performance expert specializing in SQL optimization. Analyze the following query and database context:

## Database Engine: [MySQL/PostgreSQL/SQLite/SQL Server]

## Table Schema:
[Paste your CREATE TABLE statements or describe your schema]

## The Slow Query:
[Paste your query here]

## Current Performance:
- Execution time: [e.g., 3.2 seconds]
- Rows scanned: [if known]
- Current indexes: [list existing indexes]

## Your Analysis:
1. **Execution Plan Review**: Explain what the database is doing step by step in plain language. Identify full table scans, inefficient joins, and sorting bottlenecks.
2. **Index Recommendations**: Suggest specific indexes with CREATE INDEX statements. Explain which part of the query each index optimizes.
3. **Query Rewrite**: Provide an optimized version of the query. Show multiple approaches if applicable (subquery vs JOIN, EXISTS vs IN) with pros/cons.
4. **Schema Suggestions**: If the schema itself is causing issues, suggest normalization/denormalization changes.
5. **Caching Strategy**: Recommend if and how this query result should be cached.

Provide the expected performance improvement estimate for each suggestion.

How to Use This Prompt

1. Always specify your database engine (MySQL, PostgreSQL, etc.) — optimization strategies differ significantly.
2. Include your CREATE TABLE statements for accurate analysis.
3. If you have EXPLAIN/EXPLAIN ANALYZE output, paste it — this dramatically improves the AI's recommendations.
4. Mention the table sizes (row counts) so the AI can gauge the impact.
5. For complex queries with multiple joins, include sample data if possible.

Tags 5