Key takeaway: Most database performance problems are a small number of specific slow queries, and the practical path is to find those queries first — through query logging, slow query logs, or monitoring — and then examine why each one is slow, rather than guessing at the cause or optimising broadly.
Finding the Slow Queries First
Before optimising anything, it is worth knowing which queries are actually slow, because the queries that are slow in production are frequently not the ones anyone would guess — a query that was fine with a small dataset can become slow as data grows, and the slowest query in a system is often in a code path nobody thinks to examine.
Slow query logs, database monitoring, and application-level query timing all surface the specific queries that are taking the most time or running most frequently, and this data is the starting point — optimising a query that is not actually slow is wasted effort, while the genuinely slow ones are often a small, identifiable set.
Reading the Query Plan
Once a slow query is identified, the database’s query plan — produced by EXPLAIN or its equivalent — shows how the database intends to execute it, and this is where the actual cause usually becomes visible. The most common finding is a full table scan where an index could be used, which the plan reveals directly.
| Plan indicator | What it means |
|---|---|
| Full table scan | The database is reading every row; an index may help |
| Index scan | The database is using an index; may still be slow if selective |
| Large row count examined | The query is touching far more rows than it returns |
| Sort or temporary table | The query is doing expensive extra work |
The plan tells you whether the database is using an index, how many rows it is examining, and where the expensive operations are, which is far more useful than guessing at the cause.
The Common Fixes
The most common fix is adding an index that the query can use — an index on the columns used in the WHERE clause, or a covering index that includes the columns being selected — which can turn a full table scan into an index lookup. The query plan will show whether a newly added index is actually being used.
Other common fixes include rewriting the query to avoid expensive operations — avoiding functions on indexed columns that prevent index use, reducing the amount of data selected, or restructuring a query that forces a large sort or temporary table. The query plan shows whether these changes actually help.
The Trap of Premature Optimisation
The counterpoint is that not every query needs to be fast, and optimising queries that are not actually slow is a form of premature optimisation — the right target is the queries that are actually slow and actually matter, which is why finding them first is the essential first step, rather than optimising broadly or guessing.
The Bottom Line
Find the actually slow queries first through slow query logs and monitoring, since the slowest queries are often not the ones anyone would guess. Examine each slow query’s plan to see whether it is using an index and how many rows it examines, and apply targeted fixes like adding an index or rewriting the query, verifying with the plan that the change actually helps rather than optimising queries that are not actually slow.
