Query Optimisation

Definition

Query optimisation is the discipline of reducing the work a query causes: rows examined, data transferred, round trips made, and locks held. Most real gains come from structural changes, not from SQL cleverness.

The order to work in

1. Find the actual cost. The slowest single query is often not the problem. Total time equals frequency times duration: a 20 ms query run 400 times per request dominates a 2-second report run hourly. Rank by aggregate time, not by worst case.

2. Eliminate N+1. The most common and most expensive pattern in application code: a loop that issues one query per row. It is invisible in the database's slow-query log because each query is fast. It shows up as a request that makes 300 round trips. Fix by eager loading, a join, or a batched IN query.

3. Reduce rows before joining. Filter early. A join over filtered sets is dramatically cheaper than a join over full tables followed by a filter.

4. Select only needed columns. SELECT * over a wide table with large text columns moves enormous amounts of data for no reason, and prevents covering-index optimisations.

5. Then consider indexes and rewriting.

Patterns worth knowing

• Keyset pagination over offset pagination. OFFSET 100000 makes the engine produce and discard 100,000 rows. Paginating on WHERE id > last_seen_id ORDER BY id LIMIT 50 is constant time regardless of depth. Deep pagination is a recurring cause of database load that looks like a traffic problem.
• Batching writes. One statement inserting 1,000 rows beats 1,000 statements by orders of magnitude, mostly through round-trip elimination.
• Avoid COUNT(*) on large tables for UI purposes. An approximate count, a cached count, or "showing the first 1,000 results" is almost always acceptable and thousands of times cheaper.
• Beware SELECT ... FOR UPDATE held across application logic, which turns a database lock into a queue whose length is determined by your slowest code path.

Industry example

Developer platforms like GitHub operate at a scale where the pathological case is the norm: a repository with hundreds of thousands of issues, a pull request touching thousands of files, an organisation with tens of thousands of members. Queries that are fine against the median object fall over on the outlier.

The architectural response is to treat bounded work per request as a design rule rather than an optimisation: pagination everywhere with hard maximums, timeouts on individual queries, and refusing requests that would require unbounded work rather than attempting them. A query that cannot be made fast should be made asynchronous or refused, not allowed to consume a connection for 40 seconds.

Failure scenarios

• Optimising the query that appears in the slow log while an N+1 pattern generates ten times its load invisibly.
• Adding an index that helps one query and slows every write on a hot table.
• A "temporary" report query run against the primary during business hours.
• Missing timeouts, so one pathological query holds a connection until the pool is exhausted.

Interview question

"An endpoint's p99 is 4 seconds. The database's slow query log is empty. What is happening and how do you confirm it?"