Query Optimization 101

SELECT * is bad.

Overview

Query optimization 101 writes queries that scale with data growth. Schema design sets the ceiling; query patterns determine whether the team approaches it. The discipline replaces speculation with the query plan.

• SELECT * is bad. Per-query explicit columns; reduces bytes transferred and avoids surprises when the schema grows.
• EXPLAIN ANALYZE. Per-query actual plan; supports investigation by showing what the planner actually does, not what you expected.
• Right indexes. Per-query supporting index; the index that matches the access pattern, not the index that felt complete.
• Avoid N+1 plus LIMIT. Per-loop batching prevents N+1; per-query LIMIT prevents accidental full-table scans on user-facing paths.

The approach

The practical approach: EXPLAIN ANALYZE before optimisation, indexes that match access patterns, batching for N+1, explicit columns, documented rationale per non-obvious query. The team’s discipline produces fast queries that survive data growth.

• EXPLAIN ANALYZE. Per-query actual plan; the planner output is the source of truth, not the developer’s mental model.
• Right indexes. Per-query supporting index; matches the WHERE clause and ORDER BY, not the column the developer expected.
• Avoid N+1. Per-loop batching; replace iterative single-row queries with batched IN-clauses or joins.
• Explicit columns plus documented rationale. Per-query explicit column list; per-query rationale committed for non-obvious choices.

Why this compounds

Query optimization discipline compounds across services. Each optimized query produces ongoing performance; the team’s database expertise grows; new queries inherit the patterns.

• Better query performance. Right query patterns support throughput; the database serves the workload at design capacity.
• Better operational fit. Right query for the workload; the application uses the database the way the database expects to be used.
• Better engineering culture. Plan-driven decisions replace tribal preference; speculation gets replaced with EXPLAIN output.
• Institutional knowledge. Each query teaches database patterns; the team’s database engineering muscle grows.

Query optimization discipline is a database discipline that pays off across years. Nova AI Ops integrates with database telemetry, surfaces patterns, and supports the team’s database engineering discipline.