Query Optimization

Query optimisation is where database performance is actually won. My method starts with measurement — query plans, execution stats, and real workloads — before touching a single line of SQL.

The levers are the familiar ones, applied with judgement: index design matched to access patterns, covering indexes, avoiding antimodern patterns, and restructuring queries or schema where the plan demands it. Caching sits above the DB to protect the hot path.

I treat a slow query as evidence, not an opinion. A clear rewrite path — plan analysis, index addition, schema or data-model change — turns latency complaints into predictable, verified wins.

Measurement first is the discipline that separates tuning from guessing. Before changing anything I read the query plan and the execution stats, because an optimisation aimed at the wrong bottleneck is just added complexity.

Index design is matched to the real access pattern. I build composite and covering indexes around the actual queries the workload runs, and let the plan confirm they are used — rather than adding indexes reactively and hoping.

Anti-pattern removal is guided by the plan. Full scans where an index belongs, functions wrapping indexed columns, and implicit type casts are surfaced by analysis and removed at the source instead of patched around.

Where the plan demands it, the schema adjusts. Restructuring a query or reshaping the data model for how it is actually read is often the honest fix for a latency problem that index-addiction tries to paper over.

Caching sits above the database to protect the hot path. Expensive-but-slow-changing computations are cached at the right layer, so the queries users actually hit are rarely the expensive ones.

Every win is verified. After a change I re-read the plan and re-run the metric, confirming the improvement rather than assuming it — which is how an optimisation practice stays evidence-driven and repeatable.

I measure before I change. The query plan and execution stats identify the real bottleneck, and an optimisation aimed at the wrong place is just added complexity.

Indexes are matched to the actual access pattern. Composite and covering indexes serve the queries the workload really runs, and the plan confirms they are used.

Anti-patterns are removed at the source. Full scans where an index belongs and functions wrapping indexed columns are surfaced by analysis and fixed rather than patched around.

Where the plan demands it, the schema adjusts. Restructuring a query or reshaping the model for how it is read is often the honest fix for a latency problem.

Caching sits above the database to protect the hot path, so the queries users actually hit are rarely the expensive ones.

Every change is verified. I re-read the plan and re-run the metric after each change, confirming the win rather than assuming it.

Statistics are kept current in the sense of responsible database upkeep — for engines that rely on them — so the optimiser is not flying blind.

The methodology transfers across engines. The explain-first discipline applies to Postgres, MySQL, and any relational store.

Slow query logs drive the list of what to fix, so optimisation effort goes where the evidence says it matters, in priority order.