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