sql-query-optimization

# SQL Query Optimization

**Status**: Production Ready ✅
**Last Updated**: 2025-12-15
**Latest Versions**: PostgreSQL 17, MySQL 8.4
**Dependencies**: None

---

## Quick Start (10 Minutes)

### 1. Identify Slow Query

```sql
-- PostgreSQL: Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
```

### 2. Analyze with EXPLAIN

```sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE user_id = 123;

-- Look for:
-- - Seq Scan on large tables → needs index
-- - High "Rows Removed by Filter" → poor selectivity
-- - Temp read/written → increase work_mem
```

### 3. Create Index

```sql
-- Add missing index
CREATE INDEX CONCURRENTLY idx_orders_user
ON orders(user_id);

-- Verify improvement
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123;
-- Execution time should drop 10-100x
```

---

## Critical Rules

### Always Do ✓

| Rule | Why | Example |
|------|-----|---------|
| Index foreign keys | JOINs need indexed columns | `CREATE INDEX idx_orders_user ON orders(user_id)` |
| Use EXPLAIN ANALYZE before production | Verify query plan is optimal | `EXPLAIN (ANALYZE, BUFFERS) <query>` |
| Select specific columns | Reduces data transfer 90% | `SELECT id, name FROM users` not `SELECT *` |
| Add LIMIT to unbounded queries | Prevents memory exhaustion | `SELECT * FROM logs ORDER BY id LIMIT 100` |
| Use prepared statements | Prevents SQL injection + faster | `db.query('SELECT * FROM users WHERE id = $1', [id])` |
| Run ANALYZE after bulk operations | Updates query planner statistics | `ANALYZE table_name` |
| Monitor pg_stat_statements | Track query performance over time | Review daily for regressions |
| Use connection pooling | Reduces connection overhead 10x | `new Pool({ max: 20 })` |

### Never Do ✗

| Anti-Pattern | Problem | Fix |
|--------------|---------|-----|
| SELECT * in production | Fetches