| 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 designpostgres-patterns — PostgreSQL specificsnosql-expert — NoSQL alternativesdb-review — database code review
