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

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