optimizing-sqllisted

# SQL Optimization Provide tactical guidance for optimizing SQL query performance across PostgreSQL, MySQL, and SQL Server through execution plan analysis, strategic indexing, and query rewriting. ## When to Use This Skill Trigger this skill when encountering: - Slow query performance or database timeouts - Analyzing EXPLAIN plans or execution plans - Determining index requirements - Rewriting inefficient queries - Identifying query anti-patterns (N+1, SELECT *, correlated subqueries) - Database-specific optimization needs (PostgreSQL, MySQL, SQL Server) ## Core Optimization Workflow ### Step 1: Analyze Query Performance Run execution plan analysis to identify bottlenecks: **PostgreSQL:** ```sql EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com'; ``` **MySQL:** ```sql EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 5; ``` **SQL Server:** Use SQL Server Management Studio: Display Estimated Execution Plan (Ctrl+L) **Key Metrics to Monitor:** - **Cost**: Estimated resource consumption - **Rows**: Number of rows processed (estimated vs actual) - **Scan Type**: Sequential scan vs index scan - **Execution Time**: Actual time spent on operation For detailed execution plan interpretation, see `references/explain-guide.md`. ### Step 2: Identify Optimization Opportunities **Common Red Flags:** | Indicator | Problem | Solution | |-----------|---------|----------| | Seq Scan / Table Scan | Full table scan on large table | Add index on filter