Database query optimization

Query optimization makes database access cheap and predictable: right indexes, selective predicates, pagination shape, and eliminating N+1 patterns. Always inspect **EXPLAIN** (or equivalent) on production-like data volumes.

| Problem | Fix direction | |---------|---------------| | N+1 | JOIN, batch `WHERE id IN (...)`, DataLoader | | Full table scan | Index matching WHERE/ORDER BY | | Large OFFSET | Keyset pagination | | Over-fetching | Select only needed columns |

					EXPLAIN ANALYZE
SELECT id, email FROM users WHERE tenant_id = $1 AND status = 'active' LIMIT 50;
				

ORMs hide query count—log queries per request in staging. Index design must match real filters; unused indexes slow writes.

On interviews: covering index; N+1 in Prisma/TypeORM; when denormalization is justified; read replica lag vs fresh reads.

Common pitfalls: indexing every column; caching toxic queries; pagination with huge OFFSET on million-row tables.

The trade-off is normalized schema purity versus read patterns and index maintenance cost.

Checklist:

• EXPLAIN on hot queries.
• Count queries per API call.
• Match indexes to WHERE/ORDER BY.
• Measure rows examined vs returned.