sql-optimization-patterns

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