Good Query Optimisation Starts With Careful Measurement

Slow queries usually come from doing too much work

Most newcomers meet query optimisation the moment a screen takes four seconds to appear. The first instinct is to blame the database engine or the server, yet the real cause is often simpler: the query asked for far more work than the answer required. A database is perfectly willing to read an entire table, sort a million rows in temporary memory and stitch three datasets together, because every one of those steps is legal. Optimisation is the craft of trimming that work until only the necessary part remains, and the reward is visible in milliseconds.

That craft begins with a healthy suspicion of default behaviour. Databases guess, and their guesses depend on the data they can see. When a table holds ten rows during development and ten million rows in production, the same innocent query can turn into a long wait. Thinking in terms of work rather than speed keeps the goal clear: fewer rows touched, fewer rows shuffled and fewer rows written to temporary space.

An execution plan shows where the time goes

Every serious database can explain, in words and numbers, exactly how it intends to answer a query. That explanation is usually called an execution plan, and it is the most useful map a beginner can learn to read. The plan lists the steps the engine will take, the order of those steps, and an estimate of how many rows each one will produce. Reading the plan before changing anything prevents guesswork, because it shows whether the cost sits in a scan, a sort, a join or a filter.

The estimates deserve particular attention. When a plan believes a filter will match twenty rows and it actually matches two million, every later decision suffers: the wrong join order, the wrong algorithm, the wrong index. Statistics drive these estimates, so a table that has grown quickly may still be described by stale numbers. Refreshing statistics is the quiet fix that saves more time than most rewrites, and it costs almost nothing to arrange.

A plan also exposes temporary work that never reaches the final result. Spilling rows to disk, sorting without a supporting index and scanning the same table twice all appear once you know where to look. The skill is not memorising every symbol in the output; it is recognising the one step whose row count is wildly larger than the rows you actually requested.

Filtering rows early shrinks every later step

Filters are the cheapest tool available, because a row removed early never reaches a sort, a join or an aggregate. An index is best imagined as a sorted shortcut, one that lets the engine leap to the rows you want instead of reading everything else. Not every column deserves an index, since an index on every field simply slows every write. Choose the columns that narrow the result fastest, and let the rest stay ordinary.

• Filter on the columns that remove the most rows.
• Return only the columns the screen truly needs.
• Join on keys that already carry an index.
• Request a bounded number of rows when a page is all you need.

Type mismatches quietly defeat indexes. Comparing a numeric column against text, wrapping a column inside a function, or placing a wildcard at the start of a pattern all force the engine to inspect every row. A small rewrite often restores the shortcut without changing the answer. The same principle applies to joins: matching indexed keys keeps the work proportional to the result, while matching on expressions turns every row into a fresh comparison.

A second opinion catches confident performance mistakes

Optimisation invites overconfidence, because a faster result feels like a correct one. A rewrite can appear to be quick simply because the data changed, the cache was warm, or the answer was quietly different. A second opinion is the cheapest defence available, and it helps at every level of experience. A colleague may spot a missing predicate, a review may reveal a rare case that the rewrite breaks, and a fresh pair of eyes may simply ask for evidence.

Modern assistants make that second opinion easy to request. You might hand a slow statement to Claude Sonnet 5.5 for an alternative formulation, then ask Gemini 3.1 Pro to criticise both versions against the plan you captured. Treating the replies as hypotheses rather than verdicts keeps the process honest, since every suggestion still has to survive measurement on your own data.

This is also where a dependable relay earns its place in a workflow. BestRelay is a quality-first API relay that forwards each request to the official model named in it, with no substitution behind the scenes, covering providers such as Claude, GPT, Gemini, DeepSeek, Kimi, GLM, Qwen and Grok. It works with Claude Code, Codex, Gemini CLI and any OpenAI SDK, so a review step can sit inside the tools a team already uses. Billing is pay-as-you-go from the token counts the provider reports, with cache reads and writes priced separately, and members earn a referral commission when people they invite top up. Create a BestRelay account and try the review on your slowest query.

Careful measurement keeps improvements honest and repeatable

Every optimisation deserves a number attached to it. Record the timing before the change, the timing after, and the conditions of both runs, because a warm cache and a cold cache tell different stories. Run the query against data that resembles production in size, since a fix that shines on a thousand rows can collapse on a million. Compare not only speed but also the rows returned, because a fast wrong answer is the most expensive mistake of all.

Plans and statistics change as data grows, so a result that was true last month may drift. Rechecking the plan after a release is a small habit with a large payoff, and it turns optimisation from an emergency into routine maintenance. The deeper reward is judgement: after enough cycles you begin to feel the shape of an expensive query before you run it, and you write it carefully the first time. Measured, reviewed and revisited, query optimisation becomes a craft rather than a rescue, and the screens your users touch stay fast for years.