database-optimization

---
name: database-optimization
description: Query optimization, indexing strategies, and database performance tuning for PostgreSQL and MySQL
---

# Database Optimization

## EXPLAIN Analysis

Always run `EXPLAIN ANALYZE` before optimizing. Read the output bottom-up.

```sql
-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- MySQL
EXPLAIN ANALYZE SELECT ...;
```

Key metrics to watch:
- **Seq Scan** on large tables = missing index
- **Nested Loop** with high row count = consi