Analyzes and optimizes SQL queries with execution plan analysis, index recommendations, and performance improvements.
You are a database performance expert who optimizes SQL queries. Optimize these queries for better performance. [PASTE SQL QUERIES] Context: - Database: [POSTGRESQL / MYSQL / MSSQL / OTHER] - Table sizes: [ROW COUNTS] - Current indexes: [LIST EXISTING INDEXES] - Query frequency: [HOW OFTEN RUN] - Current execution time: [IF KNOWN] - Explain plan: [PASTE IF AVAILABLE] Provide: **Query Analysis** For each query: 1. **Current Query** ```sql [Original query] ``` 2. **Execution Plan Analysis** - Scan types identified - Join methods used - Estimated vs actual rows - Cost breakdown 3. **Issues Identified** - Full table scans - Missing indexes - Inefficient joins - Subquery issues - Implicit conversions - Over-fetching 4. **Optimized Query** ```sql [Optimized query with comments] ``` 5. **Optimization Explanation** - What changed and why - Expected improvement **Index Recommendations** ```sql -- Recommended indexes with rationale CREATE INDEX idx_name ON table(columns) -- Why: [explanation] -- Impact: [read improvement vs write cost] ``` **Query Rewrite Patterns** - EXISTS vs IN - JOIN vs subquery - CTE vs derived table - Window functions opportunities **Schema Suggestions** - Denormalization opportunities - Partitioning strategy - Materialized views **Monitoring Queries** ```sql -- Query to identify slow queries -- Index usage statistics -- Table statistics ``` **Testing Plan** - Benchmark current performance - Test with production-like data - Compare execution plans - Measure improvement **Maintenance Considerations** - Statistics update schedule - Index maintenance - Query plan cache
You are a database performance expert who optimizes SQL queries. Optimize these queries for better performance. [PASTE SQL QUERIES] Context: - Database: [POSTGRESQL / MYSQL / MSSQL / OTHER] - Table sizes: [ROW COUNTS] - Current indexes: [LIST EXISTING INDEXES] - Query frequency: [HOW OFTEN RUN] - Current execution time: [IF KNOWN] - Explain plan: [PASTE IF AVAILABLE] Provide: **Query Analysis** For each query: 1. **Current Query** ```sql [Original query] ``` 2. **Execution Plan Analysis** - Scan types identified - Join methods used - Estimated vs actual rows - Cost breakdown 3. **Issues Identified** - Full table scans - Missing indexes - Inefficient joins - Subquery issues - Implicit conversions - Over-fetching 4. **Optimized Query** ```sql [Optimized query with comments] ``` 5. **Optimization Explanation** - What changed and why - Expected improvement **Index Recommendations** ```sql -- Recommended indexes with rationale CREATE INDEX idx_name ON table(columns) -- Why: [explanation] -- Impact: [read improvement vs write cost] ``` **Query Rewrite Patterns** - EXISTS vs IN - JOIN vs subquery - CTE vs derived table - Window functions opportunities **Schema Suggestions** - Denormalization opportunities - Partitioning strategy - Materialized views **Monitoring Queries** ```sql -- Query to identify slow queries -- Index usage statistics -- Table statistics ``` **Testing Plan** - Benchmark current performance - Test with production-like data - Compare execution plans - Measure improvement **Maintenance Considerations** - Statistics update schedule - Index maintenance - Query plan cache
This prompt is released under CC0 (Public Domain). You are free to use it for any purpose without attribution.
Explore similar prompts based on category and tags
Creates comprehensive Architecture Decision Records with options analysis, decision matrices, and consequence documentation.
Conducts thorough code reviews covering security, performance, maintainability, and best practices with specific fix suggestions.
Analyzes complex error stack traces to identify root causes and provide specific code fixes.
Identifies security vulnerabilities with fixes, OWASP analysis, and comprehensive hardening recommendations.