Many database scaling problems are really query problems in disguise. A missing index, a query with no limit, an accidental table scan, or an N+1 pattern can make a healthy database look overloaded. Before adding replicas, sharding, caches, or bigger servers, ask a simpler question first: what work is this query asking the database to do?

Query optimization means helping database reads and writes do less unnecessary work while still returning the right results. In real systems, one rewritten query or one well-chosen index can remove a bottleneck that looked like it needed a whole new layer of servers and services.

Good optimization starts with measurement. First find the slow query, read its execution plan, and confirm where the time is going. Then change one thing and measure again. This chapter explains how the database chooses a query plan, why common queries get slow, and how experienced engineers usually fix them.

Slow queries hurt more than the single request that triggered them.

A bad query does not only hurt one request. It can make pages slower, burn CPU and memory, read too much from disk, hold locks for too long, push useful data out of memory, slow down other queries, and force you onto larger machines.

At small scale, a query that scans 50,000 rows may not matter. At high traffic, the same query running hundreds of times per second can dominate the database.

The goal is simple: make important queries predictable, limited, and easy for the database to execute. Clever SQL is rarely the point. Clear SQL that does the right amount of work usually wins.

Before optimizing SQL, understand the basic path a database follows.

Parse

The database checks syntax and resolves table and column names.

Plan

The optimizer chooses a plan: which indexes to use, which tables to join first, how to join them, how to sort, how to group, and how many rows it expects at each step.

It makes those choices using statistics about the data. If those statistics are old, or if the data is unevenly distributed, the database may choose a plan that looks good on paper but performs badly in practice.

Execute

The database runs the plan. This is where it reads pages, filters rows, joins tables, sorts data, aggregates results, and returns rows to the client.

Most query tuning is about improving the plan or reducing the amount of work done during execution.