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

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