SQL Query Optimizer
The SQL Query Optimizer takes a slow or poorly performing SQL query and produces a comprehensive optimization analysis. It examines the query structure, identifies performance bottlenecks, recommends index strategies, and provides rewritten versions with explanations of why each change improves performance.
Backend developers, database administrators, and data analysts use this template when they encounter slow queries in production, during performance audits, or when migrating to larger datasets that expose previously hidden inefficiencies. It works with PostgreSQL, MySQL, SQL Server, and other major relational databases.
The prompt is structured to go beyond surface-level suggestions like "add an index." It asks for the database engine and schema context so the AI can provide engine-specific recommendations. It separates quick wins from structural changes, giving you a prioritized path from the fastest fix to the most impactful refactor. The before/after comparison format makes it easy to verify improvements.
This prompt is just the starting point
Score it with AI, optimize it with one click, track versions, and build your prompt library.
The Prompt
Analyze the following SQL query for performance issues and provide an optimized version: **Database Engine**: [PostgreSQL / MySQL / SQL Server / SQLite / Other] **Current Execution Time** (if known): [e.g., 4.2 seconds] **Table Schema** (relevant tables and columns): ```sql [PASTE CREATE TABLE STATEMENTS OR DESCRIBE THE SCHEMA: table names, column names, data types, existing indexes, approximate row counts] ``` **The Slow Query**: ```sql [PASTE YOUR SQL QUERY HERE] ``` **Context**: [WHAT DOES THIS QUERY DO? e.g., "Fetches all orders with customer details for the last 30 days, used in a dashboard endpoint called 50 times per minute"] Provide your analysis in this structure: 1. **Query Assessment**: In 2-3 sentences, describe what the query does and identify the most likely performance bottleneck. 2. **Issue Breakdown**: List every performance problem you can identify, ordered by impact. For each issue: - What: Name the specific problem (e.g., "Full table scan on orders table", "Correlated subquery executing per row") - Why it hurts: Explain the performance impact in concrete terms - Fix: Describe the solution 3. **Index Recommendations**: Suggest specific indexes to create, including: - The exact CREATE INDEX statement - Which part of the query each index helps - Whether it is a single-column, composite, partial, or covering index - Any trade-offs (write overhead, storage cost) 4. **Optimized Query**: Write the rewritten query with inline comments explaining each change. If there are multiple optimization levels, provide: - **Quick Fix**: Minimal changes for immediate improvement (e.g., adding a missing WHERE clause, fixing a join order) - **Full Rewrite**: The best-performing version, which may restructure the query significantly (e.g., replacing subqueries with CTEs or window functions, materializing intermediate results) 5. **Expected Impact**: Estimate the performance improvement for each optimization level (e.g., "The index addition alone should reduce this from ~4s to ~200ms based on eliminating the full table scan"). 6. **Verification Steps**: Provide the EXPLAIN or EXPLAIN ANALYZE command to run before and after, and explain what to look for in the output to confirm the optimization is working.
Usage Tips
- Always include the schema: A query cannot be optimized in isolation. At minimum, provide table names, column types, existing indexes, and approximate row counts. The more schema context, the more precise the recommendations.
- Share the EXPLAIN output if you have it: Paste the execution plan alongside the query. This eliminates guesswork and lets the AI pinpoint the exact bottleneck rather than speculating.
- Specify your database engine: PostgreSQL, MySQL, and SQL Server have different optimizers, different index types, and different syntax for hints and CTEs. Engine-specific advice is dramatically better than generic SQL advice.
- Test the Quick Fix first: The optimized query often includes both a quick fix and a full rewrite. Deploy the quick fix first to get immediate relief, then validate and deploy the full rewrite during a proper development cycle.
- Watch for trade-offs: Every new index speeds up reads but slows writes. If your table has heavy write traffic, flag this in the context so the AI can recommend partial indexes or other write-friendly strategies.
Get more from this prompt
Save it, score it with AI, optimize it, and track every version. Free to start.