Database Optimisation: From Slow Queries to Sub-Millisecond Response

Almost every slow application has a slow database underneath it, and almost every slow database is slow for one of about six reasons. That is encouraging news: database performance is one of the most tractable areas in software engineering, because the mechanisms are well understood, the measurements are precise, and the fixes are usually small. What makes it feel mysterious is that most teams optimise by guessing rather than by reading what the database is actually telling them.

This guide covers the discipline: how to find the queries that matter, how to read an execution plan, how indexes actually work, why locking and connection management cause problems that look like slow queries, and the schema decisions that determine your ceiling. It assumes you can write SQL but have never tuned a database deliberately.

• How to find the queries actually costing you, rather than the ones that feel slow
• How indexes work, and why column order in a composite index decides everything
• Reading an execution plan without memorising terminology
• The query patterns that defeat indexes silently
• Locking, connections and the problems that masquerade as slow queries
• Caching, partitioning and scaling, in the order they should be attempted

1. Measure before you touch anything
2. How a query is executed
3. Indexes, properly understood
4. Composite indexes and column order
5. Reading an execution plan
6. Query patterns that defeat indexes
7. The N+1 problem
8. Joins and cardinality
9. Pagination at scale
10. Statistics and the planner
11. Locking and concurrency
12. Connections and pooling
13. Schema decisions
14. Caching
15. When to scale, and how
16. Twelve mistakes
17. A worked example: one endpoint, taken apart
18. Frequently asked questions

1. Measure before you touch anything

The first rule of database performance is that intuition about which query is slow is reliably wrong. Databases keep detailed statistics; use them.

Three sources answer nearly every question. The slow query log records queries exceeding a threshold — set it low temporarily to see what is really happening. The statement statistics view aggregates by query shape, showing total time, call count and mean duration. And application tracing shows which queries a given request actually issues, which is how you discover a page making four hundred of them.

The metric that matters is total time, not mean duration. A query taking two seconds and running once an hour is less important than one taking four milliseconds and running two hundred thousand times an hour. Teams consistently optimise the dramatic slow query and ignore the accumulated cost of the fast one, which is usually where the load actually is.

Sort your statement statistics by total execution time and look at the top ten. In most systems those ten queries account for the overwhelming majority of database load, and three of them will have an obvious fix.

2. How a query is executed

Understanding the stages makes performance behaviour predictable rather than mysterious.

The query is parsed into a syntax tree. The planner then considers ways to satisfy it — which indexes to use, which join order, which join algorithm — and estimates the cost of each using statistics about the data. It picks the cheapest estimate and the executor runs it, reading pages from memory where possible and from disk where not.

Two facts from that sequence explain most surprises. The planner works from estimates, not from the data itself, so stale or insufficient statistics produce bad plans that look inexplicable. And the unit of I/O is a page, not a row — reading one row means reading the whole page containing it, which is why data locality and row size matter more than they appear to.

3. Indexes, properly understood

An index is a separate structure that maps values to row locations, ordered so the database can find a value without scanning everything. The standard structure is a balanced tree, which means lookups cost a small number of steps regardless of table size — this is why a well-indexed query on a billion rows can be as fast as on a thousand.

What an index gives you:

• Equality lookup — find rows where a column equals a value.
• Range scans — find rows between two values, because the tree is ordered.
• Ordered retrieval — satisfy an order-by clause without sorting.
• Uniqueness enforcement, as a side effect.
• Index-only scans — if every column the query needs is in the index, the table itself is never read. This is the largest single win available and is routinely overlooked.

What an index costs: storage, and slower writes, because every insert, update and delete must maintain every affected index. A table with twelve indexes has slow writes, and several of those indexes are probably unused. Most databases expose index usage statistics; auditing and dropping unused indexes is a quick and reliable improvement.

4. Composite indexes and column order

This is the single most important technical detail in practical database tuning, and the one most often misunderstood.

An index on several columns is ordered by the first column, then the second within that, and so on — like a phone book sorted by surname then forename. You can look up everyone with a given surname efficiently, and everyone with a given surname and forename, but you cannot efficiently find everyone with a given forename regardless of surname.

The practical rule: an index on columns A, B, C can serve queries filtering on A, on A and B, or on A, B and C. It cannot efficiently serve a query filtering only on B or only on C.

The ordering heuristic that works: equality columns first, then the range column, then columns needed only for ordering or output. A query filtering on a tenant identifier and a status, then ordering by date, wants an index on tenant, status, date — in that order. Reverse it and the index serves the query far less effectively.

The corollary is that fewer, wider indexes beat many narrow ones. Three separate single-column indexes are usually worse than one well-ordered composite index, because the database can only combine them awkwardly.

5. Reading an execution plan

Every database can show how it intends to run a query, and how it actually ran it. Read the actual plan rather than the estimate wherever your database offers it, because the difference between estimated and actual row counts is the most informative signal available.

The access methods, in rough order of desirability:

Three things to look for, in order:

A large gap between estimated and actual rows. If the planner expected ten rows and found ten thousand, every decision downstream was made on wrong information. This almost always means stale statistics or a correlation the planner cannot see.

Sequential scans on large tables where a filter should have used an index. Either the index does not exist, or something in the query prevents its use — see the next section.

Sorts and hash operations spilling to disk. When an operation exceeds its memory allowance it writes to temporary storage, which is orders of magnitude slower. Visible in the plan, and frequently fixed by either an index that provides the required order or a modest memory setting increase.

6. Query patterns that defeat indexes

A common and frustrating situation: the index exists, and the database ignores it. Almost always one of these.

• A function applied to the indexed column. Wrapping a column in a function means the index on the raw column cannot be used, because the index stores the raw values. Restructure the query to leave the column bare, or create an index on the expression.
• A leading wildcard in a pattern match. An index can find values starting with a prefix; it cannot find values containing a substring. That requires a different kind of index designed for text search.
• Type mismatch. Comparing a numeric column to a string value forces a conversion, which defeats the index. This is a common and invisible cause, particularly with identifiers stored as one type and passed as another.
• Negation and inequality. Conditions that match most rows cannot be served efficiently by an index, because reading the index and then most of the table is slower than reading the table.
• Low selectivity. An index on a column with three distinct values is rarely useful on its own — the database correctly concludes that scanning is cheaper. Such columns belong as later elements of a composite index, not as the lead.
• Wrong column order in a composite index, as covered above. The index exists and does not apply.

7. The N+1 problem

The most common performance problem in application code, and it is invisible in the database's slow query log because each individual query is fast.

The shape: fetch a list of fifty items, then for each item fetch something related. One query becomes fifty-one, and each carries network latency and connection overhead. The database reports fifty-one fast queries; the user experiences a slow page.

The fixes, in order of preference: fetch the related data in one query with a join or a single lookup by a set of identifiers; batch the lookups so a loop issues one query rather than fifty; or denormalise the specific value into the parent record when it is read constantly and changes rarely.

The detection method that works is application tracing showing query count per request. A threshold — no request may issue more than a handful of queries — catches this class of problem automatically, and it is one of the highest-value checks a team can add.

8. Joins and cardinality

Join performance is dominated by how many rows flow between stages, which is why cardinality — the number of rows at each step — is the thing to reason about.

Two practical principles. Filter as early as possible, so the join operates on the smallest possible sets. A condition applied after a join processes far more rows than the same condition applied before it, and while planners often rewrite this correctly, they do not always.

And index both sides of a join. A join key indexed on one side only forces the database into a strategy it would not otherwise choose. Foreign key columns are the most commonly missing indexes in real schemas, because declaring a foreign key does not create one in every database.

The join algorithm should follow from cardinality: a nested loop is excellent when the outer set is small and each lookup is indexed, and catastrophic when the outer set is large. When a plan shows a nested loop over a large set, the usual cause is an underestimate of the row count feeding it.

9. Pagination at scale

Offset pagination is the default in most frameworks and degrades badly. Skipping to the ten-thousandth row requires the database to read and discard the preceding rows, so page one is instant and page five hundred is slow. It is also unstable: rows inserted during paging cause items to be seen twice or missed.

Keyset pagination solves both. Instead of an offset, remember the sort value of the last row seen and request rows after it. The database seeks directly into the index and reads the requested number of rows, at the same cost regardless of depth. It is also stable under concurrent inserts.

The trade-off is that random access to an arbitrary page number is not possible. In practice most interfaces do not need it — infinite scroll and next-page navigation are keyset patterns already. Reserve offset pagination for small result sets where a page-number interface is genuinely required.

10. Statistics and the planner

The planner chooses based on estimates derived from sampled statistics: how many distinct values a column has, how values are distributed, how large the table is. When those are wrong, the plan is wrong, and the query is inexplicably slow despite a perfect index.

The situations that produce bad estimates:

• Stale statistics after a bulk load or a large deletion. Most databases update automatically on a threshold, which can lag significantly on a large table.
• Correlated columns. The planner assumes independence, so filtering on city and country produces an estimate far lower than reality. Extended statistics on the column group fix this where supported.
• Skewed distributions. If one value accounts for most rows, an average-based estimate is wrong for both the common and rare cases. Larger histogram samples help.
• Parameter sensitivity. A prepared statement planned for one parameter value may be a poor plan for another with very different selectivity.

When a query is slow and the plan looks unreasonable, updating statistics is the first thing to try. It is fast, safe, and resolves a surprising share of "the database suddenly got slow" incidents.

11. Locking and concurrency

Many problems reported as slow queries are actually waiting problems: the query is fast and spent nine seconds waiting for a lock.

The causes worth knowing:

Long transactions. A transaction held open while the application does other work — calling an API, waiting for user input — holds its locks throughout. This is the most common cause of lock contention and the easiest to fix: keep transactions short and never perform network calls inside one.

Hot rows. A counter row updated by every request serialises the whole application. The fix is usually structural — sharded counters, or aggregating asynchronously rather than updating in the request path.

Schema changes on large tables. Some alterations take a lock that blocks all access for the duration. On a large table that is an outage. Modern databases perform many alterations without a blocking lock, and knowing which is which for your version is essential before running a migration in production.

Deadlocks, where two transactions each hold what the other needs. The database detects and aborts one. The fix is consistent ordering — always acquire locks in the same order — and short transactions.

Long-running read queries deserve a mention too: in databases using multi-version concurrency, a long transaction prevents cleanup of old row versions, which causes tables to bloat and everything to slow down. A reporting query left running for hours can degrade the whole system without appearing to be the cause.

12. Connections and pooling

Each connection costs memory and, in some databases, a process. Opening one per request is expensive; allowing unlimited connections exhausts the server.

A connection pool maintains a set of established connections shared by the application. The counter-intuitive part is sizing: the right pool is usually far smaller than people expect. A database with a limited number of cores and disks cannot usefully execute two hundred queries at once, and attempting it produces context switching that makes everything slower. Modest pools frequently outperform large ones, and the way to find the right size is measurement rather than intuition.

Symptoms of pool exhaustion masquerade as database slowness: requests waiting to acquire a connection appear as slow requests, while the database itself is idle. Instrument pool wait time separately from query time — without that distinction, you will optimise queries that were never the problem.

13. Schema decisions

Some choices set a performance ceiling that no amount of tuning raises.

Data types. Use the narrowest type that fits. Narrower rows mean more rows per page, which means fewer pages read for the same data. This compounds across every query on the table.

Primary key choice. In databases that cluster the table by primary key, a sequential key means inserts append to the end while a random key writes across the whole structure, causing fragmentation and slower writes. Where random identifiers are required, time-ordered variants preserve locality.

Normalisation, then selective denormalisation. Start normalised, because it prevents inconsistency. Denormalise specific values that are read constantly and change rarely, deliberately and with a mechanism to keep them consistent.

Nullable columns and wide tables. A table with eighty columns where queries use six wastes I/O on every read. Splitting rarely-used columns into a separate table is a legitimate optimisation when the access patterns genuinely differ.

Structured document columns are convenient and should not become a way to avoid schema design. Fields queried regularly belong as real columns with real indexes; document types are for genuinely variable data.

14. Caching

Caching is the highest-leverage optimisation and the easiest to get wrong.

The layers, from cheapest to most involved: the database's own buffer pool, which should be sized to hold your working set — this is the single most impactful configuration setting on most servers; an application-level cache for expensive computed results; and materialised views for aggregations that are expensive to compute and tolerate slight staleness.

The hard part is invalidation. Three approaches, with honest trade-offs: time-based expiry is simple and serves stale data for the duration; event-based invalidation is correct and requires discipline to cover every write path; and versioned keys, where a change increments a version included in the cache key, avoid explicit deletion at the cost of leaving old entries to expire.

The failure worth naming is the cache stampede: a popular entry expires, and a thousand concurrent requests all miss and all hit the database simultaneously. The defences are locking so one request refreshes while others serve stale data, or refreshing proactively before expiry.

15. When to scale, and how

Scaling should be the last resort, not the first, because it is the most expensive option and it hides problems rather than fixing them. Attempt in this order:

1. Fix the queries. Indexes and query structure. Frequently produces order-of-magnitude improvements for a day of work.
2. Tune configuration. Buffer pool size, memory for sorts and joins, connection pool sizing.
3. Cache. Remove read load entirely rather than serving it faster.
4. Scale vertically. A larger machine is simple, immediate, and requires no architectural change. Cheaper than engineering time in most cases.
5. Read replicas. Route read-only traffic elsewhere. Effective for read-heavy workloads, and it introduces replication lag that the application must tolerate — a read immediately after a write may not see it.
6. Partitioning. Split a large table by a key, usually date, so queries touch one partition and old data can be dropped cheaply. Excellent for time-series data.
7. Sharding. Split data across independent databases. Genuinely difficult — cross-shard queries, rebalancing and transactions all become hard — and should be the last option considered.

16. Twelve mistakes

1. Optimising by intuition. The slow query is rarely the expensive one.
2. Ranking by mean duration. Total time is what matters.
3. Missing indexes on foreign keys. The most common gap in real schemas.
4. Wrong column order in composite indexes. The index exists and cannot be used.
5. Functions applied to indexed columns. Silently defeats the index.
6. Type mismatches in comparisons. Invisible, and it disables index use.
7. N+1 queries. Invisible in the database log, obvious in application traces.
8. Offset pagination on large tables. Slow and unstable, and easily replaced.
9. Long transactions holding locks. Especially any transaction containing a network call.
10. Oversized connection pools. More concurrency than the server can use makes everything slower.
11. Selecting every column out of habit. Prevents index-only scans and wastes I/O.
12. Scaling before fixing. Hides the problem at recurring cost.

17. A worked example: one endpoint, taken apart

Consider an order history endpoint taking four seconds. The team's instinct is that the orders table has grown too large and needs sharding. Measurement tells a different story.

Application tracing shows the request issues sixty-three queries. One fetches twenty orders; the remaining sixty-two fetch line items and a status label for each order individually. This is an N+1 problem, and it is invisible in the database's slow query log because every one of those queries takes under two milliseconds. Fixing it — fetching line items for all twenty orders in one query keyed by a set of identifiers — removes about half the elapsed time immediately.

The remaining main query still takes nearly two seconds. The execution plan shows a sequential scan on a table of several million rows, despite an index existing on the customer identifier. The cause turns out to be a type mismatch: the column is a numeric type and the application passes the value as a string, forcing a conversion that disables the index. Correcting the parameter type takes one line and drops the query to a few milliseconds.

Then the plan shows a sort spilling to disk. The query orders by date and the index covers only the customer identifier, so the database retrieves the rows and sorts them. Replacing the index with a composite on customer identifier and date — equality column first, ordering column second — allows the index to supply the order directly, and the sort disappears from the plan entirely.

One more improvement is available. The query selects every column but the endpoint uses five. Adding those five to the index converts it into an index-only scan, so the table pages are never read. The query now completes in well under a millisecond.

Pagination is fixed on the way past. The endpoint used offset pagination, which was fine on page one and slow on page fifty. Switching to keyset pagination makes every page equally fast and removes the instability where a new order shifted rows between pages.

Total elapsed time falls from four seconds to under fifty milliseconds. Nothing was sharded, no hardware changed, and the work took an afternoon. The five fixes were: eliminate N+1, correct a type mismatch, order a composite index properly, cover the query with the index, and replace offset pagination. That list resolves a large share of real-world database performance problems, which is why measurement rather than architecture is where to start.

18. Frequently asked questions

How do I know which index to add?

From the execution plan of the query you are fixing, not from a general rule. Identify the filter conditions, the join keys and the ordering, then build an index with equality columns first, the range column next, and any additional columns the query selects. Then verify the plan actually uses it — an index that exists and is not used is pure cost.

Can there be too many indexes?

Yes. Every index slows writes and consumes storage, and most real schemas contain several that no query uses. Check usage statistics, drop the unused ones, and look for redundancy — an index on one column is usually redundant if another index leads with the same column. Consolidating into fewer, well-ordered composite indexes typically improves both read and write performance.

Why did a query that was fast become slow overnight?

Most commonly stale statistics after a data volume change, causing the planner to choose a different plan. Sometimes data growth crossing a threshold where a previously reasonable sequential scan stops being reasonable. Occasionally a change in parameter values with very different selectivity. Update statistics first, then compare the current plan against what it used to be if you have it recorded.

Should we use an ORM?

They are productive and they hide query generation, which is where the N+1 problem comes from. Use one, and instrument query count per request so the hidden queries become visible. Learn how to write explicit queries in it for the paths that matter, and read the generated SQL for anything on a hot path. The problem is not the tool; it is using it without ever looking at what it produces.

How large can a single table get?

Larger than most teams assume. Hundreds of millions of rows are entirely routine with appropriate indexes, because index lookups cost a small number of steps regardless of size. What actually degrades with size is anything requiring a full scan, maintenance operations, and backup and restore duration. Partitioning helps those specifically; it does not make indexed lookups faster.

When should we add a read replica?

When the workload is genuinely read-heavy, queries are already optimised, and caching has been applied. Replicas do not help write-heavy workloads and they introduce replication lag, so the application must tolerate reading data that is momentarily stale — the classic failure is a user saving a change and then not seeing it. Route reads that tolerate lag; keep read-after-write on the primary.

Is denormalisation a good idea?

Selectively and deliberately, after normalised design has proven insufficient for a specific access pattern. Duplicate values that are read constantly and change rarely, and put a mechanism in place to keep them consistent — a trigger, an event handler or a scheduled reconciliation. Denormalising broadly from the start produces inconsistency that is far more expensive than the queries it saved.

What single thing should we do first?

Enable statement statistics and query tracing, then sort by total time and look at the top ten queries. That takes an hour and tells you exactly where your database load actually is — which is almost never where the team assumed. Every subsequent decision becomes evidential rather than speculative.

Key takeaways

• Measure first, and rank by total time. The dramatic slow query is rarely the expensive one.
• Column order in composite indexes decides everything. Equality, then range, then output columns.
• Read the actual plan. The gap between estimated and actual rows is the most informative signal available.
• N+1 queries hide from the database log. Instrument query count per request.
• Many slow queries are waiting, not working. Check locks and connection pool wait time separately.
• Scale last. Queries, configuration, caching, then hardware — in that order.

Database performance rewards discipline more than cleverness. Find the queries that cost the most, read what the database says about them, fix the specific cause, and measure again. Most systems have several order-of-magnitude improvements available for an afternoon of that, waiting behind an assumption nobody checked.