SQL Optimizer Skill

Purpose

Analyzes and optimizes SQL queries for performance, index usage, and best practices.

When to Use

• SQL query optimization
• Query performance review
• Index usage analysis
• N+1 query detection
• Slow query troubleshooting

Project Detection

• .sql files
• Query strings in code
• Database migration files
• ORM query logs

Workflow

Step 1: Analyze Query

**Database**: PostgreSQL/MySQL/SQLite
**Tables**: users, orders, products
**Query Type**: SELECT with JOINs
**Estimated Rows**: 100K+

Step 2: Select Review Areas

AskUserQuestion:

"Which areas to review?"
Options:
- Full query optimization (recommended)
- Index usage analysis
- Join optimization
- Subquery refactoring
- N+1 detection
multiSelect: true

Detection Rules

Index Usage

| Check | Recommendation | Severity | |-------|----------------|----------| | Full table scan | Add appropriate index | CRITICAL | | Index not used | Check column order | HIGH | | Too many indexes | Consolidate indexes | MEDIUM | | Missing composite index | Add multi-column index | HIGH |

-- BAD: No index on filter columns
SELECT * FROM orders
WHERE created_at > '2024-01-01'
AND status = 'pending';
-- Full table scan!

-- GOOD: Add composite index
CREATE INDEX idx_orders_status_created
ON orders(status, created_at);

-- Index order matters!
-- For WHERE status = ? AND created_at > ?
-- Index(status, created_at) ✓
-- Index(created_at, status) ✗ (less effective)

SELECT Optimization

| Check | Recommendation | Severity | |-------|----------------|----------| | SELECT * | Select specific columns | HIGH | | Unnecessary columns | Remove unused columns | MEDIUM | | No LIMIT | Add LIMIT for large results | HIGH |

-- BAD: SELECT * with large result
SELECT * FROM orders
WHERE user_id = 123;
-- Returns all columns, no limit

-- GOOD: Specific columns, limited results
SELECT id, status, total, created_at
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

JOIN Optimization

| Check | Recommendation | Severity | |-------|----------------|----------| | Cartesian product | Add join condition | CRITICAL | | Join on non-indexed column | Add index | HIGH | | Too many joins | Consider denormalization | MEDIUM | | Implicit join | Use explicit JOIN syntax | LOW |

-- BAD: Implicit join (harder to read, error-prone)
SELECT o.*, u.name
FROM orders o, users u
WHERE o.user_id = u.id;

-- GOOD: Explicit JOIN
SELECT o.id, o.total, u.name
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- BAD: Join on non-indexed column
SELECT o.*, p.name
FROM orders o
JOIN products p ON o.product_code = p.code;
-- If products.code has no index → slow!

-- FIX: Add index
CREATE INDEX idx_products_code ON products(code);

Subquery Optimization

| Check | Recommendation | Severity | |-------|----------------|----------| | Correlated subquery | Convert to JOIN | HIGH | | IN with subquery | Use EXISTS or JOIN | MEDIUM | | Subquery in SELECT | Move to JOIN | HIGH |

-- BAD: Correlated subquery (runs for each row)
SELECT *
FROM orders o
WHERE total > (
    SELECT AVG(total)
    FROM orders
    WHERE user_id = o.user_id
);

-- GOOD: Use window function
SELECT *
FROM (
    SELECT *,
           AVG(total) OVER (PARTITION BY user_id) as avg_total
    FROM orders
) sub
WHERE total > avg_total;

-- BAD: IN with large subquery
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'vip');

-- GOOD: Use EXISTS or JOIN
SELECT u.* FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.status = 'vip'
);

-- Or with JOIN
SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.status = 'vip';

N+1 Query Detection

| Check | Recommendation | Severity | |-------|----------------|----------| | Loop with query | Batch fetch | CRITICAL | | Lazy load in loop | Eager load | CRITICAL |

-- N+1 Pattern (application code)
-- Query 1: Get all users
SELECT * FROM users;

-- Then for each user (N queries):
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
SELECT * FROM orders WHERE user_id = 3;
-- ... N more queries

-- SOLUTION 1: JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- SOLUTION 2: IN query (for separate queries)
SELECT * FROM orders WHERE user_id IN (1, 2, 3, ...);

Aggregation Optimization

| Check | Recommendation | Severity | |-------|----------------|----------| | COUNT(*) on large table | Use approximate count | MEDIUM | | GROUP BY without index | Add index | HIGH | | HAVING vs WHERE | Filter early with WHERE | MEDIUM |

-- BAD: COUNT on entire table
SELECT COUNT(*) FROM orders;
-- Scans entire table

-- GOOD: Approximate count (PostgreSQL)
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'orders';

-- BAD: WHERE in HAVING
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
HAVING status = 'completed';  -- Wrong place!

-- GOOD: Filter before grouping
SELECT user_id, COUNT(*)
FROM orders
WHERE status = 'completed'  -- Filter first
GROUP BY user_id;

-- Index for GROUP BY
CREATE INDEX idx_orders_user_status
ON orders(user_id, status);

EXPLAIN Analysis

-- PostgreSQL EXPLAIN
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

-- Look for:
-- ✗ Seq Scan (full table scan)
-- ✗ Nested Loop with high rows
-- ✗ Hash Join with large hash
-- ✓ Index Scan
-- ✓ Index Only Scan
-- ✓ Bitmap Index Scan

Response Template

## SQL Query Optimization Results

**Database**: PostgreSQL 15
**Query Type**: SELECT with JOIN
**Estimated Impact**: ~10x improvement

### Index Usage
| Status | Issue | Recommendation |
|--------|-------|----------------|
| CRITICAL | Full table scan on orders | Add index on (status, created_at) |

### Join Analysis
| Status | Issue | Recommendation |
|--------|-------|----------------|
| HIGH | Non-indexed join column | Add index on products.code |

### Query Structure
| Status | Issue | Recommendation |
|--------|-------|----------------|
| HIGH | SELECT * with no LIMIT | Select specific columns, add LIMIT |

### Recommended Indexes
```sql
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
CREATE INDEX idx_products_code ON products(code);

Optimized Query

SELECT o.id, o.total, p.name
FROM orders o
INNER JOIN products p ON o.product_id = p.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 100;

## Best Practices
1. **Indexes**: Add for WHERE, JOIN, ORDER BY columns
2. **SELECT**: Only needed columns, with LIMIT
3. **JOINs**: Explicit syntax, indexed columns
4. **Subqueries**: Prefer JOINs or CTEs
5. **EXPLAIN**: Always analyze query plans

## Integration
- `schema-reviewer`: Database design
- `orm-reviewer`: ORM query patterns
- `perf-analyzer`: Application performance