---
name: sql-optimization-patterns
type: reference
description: "Provides SQL optimization patterns for query performance, indexing strategies, schema design, and database tuning. Use when optimizing slow queries, designing indexes, or tuning database performance."
paths: ["**/*.sql", "**/migrations/**"]
effort: 3
allowed-tools: Read, Glob, Grep, Bash
user-invocable: true
when_to_use: "When optimizing slow SQL queries, designing indexes, or tuning database performance"
---
# SQL Optimization Patterns
Query optimization, indexing, and performance tuning for PostgreSQL, MySQL, and SQLite.
## Index Strategy
### When to Create Index
\`\`\`sql
-- High selectivity columns (many unique values)
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Composite index: order matters (equality first, then range)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- Covering index (includes all needed columns)
CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);
\`\`\`
### When NOT to Index
- Low cardinality columns (boolean, status with few values)
- Small tables (< 1000 rows)
- Write-heavy tables with rare reads
## Query Patterns
### Avoid SELECT *
\`\`\`sql
-- Bad
SELECT * FROM orders WHERE user_id = 1;
-- Good (select only needed columns)
SELECT id, total, status FROM orders WHERE user_id = 1;
\`\`\`
### Avoid N+1 (use JOIN or subquery)
\`\`\`sql
-- Bad: N+1 queries from application
-- Good: Single query with JOIN
SELECT o.id, o.total, u.name
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';
\`\`\`
### Pagination (keyset, not OFFSET)
\`\`\`sql
-- Bad: OFFSET scans all skipped rows
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;
-- Good: Keyset pagination
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 20;
\`\`\`
## EXPLAIN ANALYZE
\`\`\`sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';
\`\`\`
Read output:
- Seq Scan = missing index
- Index Scan = good
- Nested Loop with high row count = check join strategy
## Schema Anti-Patterns
| Anti-Pattern | Problem | Fix |
|-------------|---------|-----|
| EAV (Entity-Attribute-Value) | No type safety, slow queries | Use JSONB or proper columns |
| God table | Too many columns | Normalize into related tables |
| No constraints | Data integrity issues | Add CHECK, FK, UNIQUE constraints |
| String dates | Sorting/filtering issues | Use TIMESTAMP type |
## Connection Pooling
\`\`\`
App → Pool (min: 5, max: 20) → PostgreSQL
\`\`\`
Tools: PgBouncer (PostgreSQL), ProxySQL (MySQL).
## Related Skills
- `database-architect` — schema design
- `postgres-patterns` — PostgreSQL specifics
- `nosql-expert` — NoSQL alternatives
- `db-review` — database code review