optimizing-sql

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:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

MySQL:

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 columns
High row count	Processing excessive rows	Add WHERE filter or index
Nested Loop with large outer table	Inefficient join algorithm	Index join columns
Correlated subquery	Subquery executes per row	Rewrite as JOIN or EXISTS
Sort operation on large result set	Expensive sorting	Add index matching ORDER BY

For scan type interpretation, see references/scan-types.md.

Step 3: Apply Indexing Strategies

Index Decision Framework:

Is column used in WHERE, JOIN, ORDER BY, or GROUP BY?
├─ YES → Is column selective (many unique values)?
│  ├─ YES → Is table frequently queried?
│  │  ├─ YES → ADD INDEX
│  │  └─ NO → Consider based on query frequency
│  └─ NO (low selectivity) → Skip index
└─ NO → Skip index