How do I use the "Optimize SQL Query Performance" prompt?

Copy the prompt template, fill in the variables (SQL_QUERY, DATABASE_TYPE, TABLE_SCHEMAS, PERFORMANCE_ISSUES, EXECUTION_FREQUENCY), and paste it into ChatGPT, Claude, or Gemini.

What AI tools work with this coding prompt?

This prompt works with chatgpt, claude, gemini. Simply copy and paste the template into any of these AI assistants.

Tips for getting the best results?

Always provide actual table schemas and row counts for more accurate optimization suggestions Include current execution plans (EXPLAIN ANALYZE) when possible for deeper analysis Specify your database version as optimization techniques vary between versions Mention any existing indexes to avoid redundant recommendations Test recommended optimizations on a copy of production data before implementing

coding advanced

Optimize SQL Query Performance

Get AI-powered SQL query optimization suggestions to improve performance, reduce execution time, and enhance database efficiency.

Works with: chatgptclaudegemini

Prompt Template

You are a senior database performance engineer specializing in SQL query optimization. I need you to analyze and optimize the following SQL query for better performance. **Query to optimize:** [SQL_QUERY] **Database system:** [DATABASE_TYPE] **Table schemas and row counts:** [TABLE_SCHEMAS] **Current performance issues:** [PERFORMANCE_ISSUES] **Query execution frequency:** [EXECUTION_FREQUENCY] Please provide a comprehensive optimization analysis including: 1. **Performance bottlenecks identification**: Analyze the query structure and identify specific performance issues 2. **Optimized query version**: Rewrite the query with improvements 3. **Index recommendations**: Suggest specific indexes that would improve performance 4. **Query execution plan analysis**: Explain how the optimizations will affect the execution plan 5. **Alternative approaches**: Provide 2-3 different optimization strategies if applicable 6. **Estimated performance impact**: Quantify expected improvements where possible 7. **Best practices violated**: Point out any SQL best practices that should be followed 8. **Monitoring recommendations**: Suggest metrics to track ongoing performance Format your response with clear sections and include the reasoning behind each recommendation. Focus on practical, implementable solutions that will provide measurable performance gains.

Variables to Customize

[SQL_QUERY]

The SQL query that needs optimization

Example: SELECT u.name, COUNT(o.id) as order_count, AVG(o.total) as avg_order FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at > '2023-01-01' GROUP BY u.id, u.name ORDER BY order_count DESC

[DATABASE_TYPE]

The database management system being used

Example: PostgreSQL 14

[TABLE_SCHEMAS]

Relevant table structures and row counts

Example: users table (500K rows): id, name, email, created_at; orders table (2M rows): id, user_id, total, created_at

[PERFORMANCE_ISSUES]

Specific performance problems being experienced

Example: Query takes 8-12 seconds to execute, causing timeout issues in the web application

[EXECUTION_FREQUENCY]

How often this query runs

Example: Executed 200-300 times per hour during business hours

Example Output

## Performance Bottlenecks Identified 1. **Missing indexes**: No index on `users.created_at` causing full table scan 2. **Inefficient JOIN**: LEFT JOIN with GROUP BY causing unnecessary row processing 3. **Suboptimal ORDER BY**: Sorting large result set without index support ## Optimized Query Version ```sql SELECT u.name, COALESCE(o.order_count, 0) as order_count, COALESCE(o.avg_order, 0) as avg_order FROM users u LEFT JOIN ( SELECT user_id, COUNT(*) as order_count, AVG(total) as avg_order FROM orders GROUP BY user_id ) o ON u.id = o.user_id WHERE u.created_at > '2023-01-01' ORDER BY COALESCE(o.order_count, 0) DESC; ``` ## Index Recommendations 1. `CREATE INDEX idx_users_created_at ON users(created_at);` 2. `CREATE INDEX idx_orders_user_id_total ON orders(user_id, total);` ## Expected Performance Impact - **Execution time**: Reduce from 8-12s to 200-500ms (95% improvement) - **I/O operations**: Reduce by ~80% through index usage - **CPU usage**: Lower due to reduced sorting overhead ## Alternative Approach Consider materialized views for frequently accessed aggregations, updating hourly via scheduled jobs for even better performance.

Pro Tips for Best Results

Always provide actual table schemas and row counts for more accurate optimization suggestions
Include current execution plans (EXPLAIN ANALYZE) when possible for deeper analysis
Specify your database version as optimization techniques vary between versions
Mention any existing indexes to avoid redundant recommendations
Test recommended optimizations on a copy of production data before implementing