Query Optimization: A Checklist for Developers

1. Why a checklist makes sense before escalating to a DBA

When a query is suddenly or persistently slow, in many teams the problem lands with the database administration too quickly, even though the cause often sits directly in the application code. Structured query optimization therefore does not begin with an escalation, but with a fixed sequence of checks a developer can perform independently. In practice, this checklist covers the large majority of all performance problems without deep DBA knowledge being necessary.

The value of such a checklist lies in the order: you always start with the cheapest and most informative check, the execution plan, and only work your way to more elaborate diagnostics, such as locking analysis, as needed. Unstructured query optimization, where indexes get added at random or the query gets rewritten repeatedly, wastes time and often creates new problems, such as unnecessary indexes that increase write load.

This checklist is deliberately kept database-agnostic. The concrete commands differ between MySQL, PostgreSQL and SQL Server, but the underlying approach to query optimization does not. Every step is illustrated with a concrete example, so the checklist is directly applicable in daily work.

2. Step 1: check the execution plan first

The first and most important step of any query optimization is to look at the query's execution plan before changing anything. Without this step, every change is based on guesswork instead of data. You look specifically for full table scans on large tables, for large gaps between estimated and actual row count, and for expensive sort or join operations.

In practice, a rough look at the plan is often enough to narrow down the cause. A full table scan points to a missing index, a large sort node points to missing sort support in the index, and a large gap between estimate and reality points to stale statistics. This first diagnostic step often already decides which of the following steps in the query optimization checklist is relevant next.

-- Step 1 of query optimization: always look at the plan first
EXPLAIN ANALYZE
SELECT o.order_id, c.customer_name, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.order_date DESC
LIMIT 50;

-- Watch for: Seq Scan on large tables, big estimate-vs-actual gaps,
-- unexpected Sort nodes, or nested loops with high loop counts

3. Step 2: make WHERE clauses sargable

A WHERE clause is "sargable" (Search ARGument ABLE) when the database can process it directly through an index, without having to check every row individually. Non-sargable conditions are one of the most common and easiest to fix reasons for a slow query. A typical example: a function wrapped around an indexed column, such as WHERE YEAR(order_date) = 2026 instead of a range comparison, prevents index usage entirely, because the database has to evaluate the function for every row individually.

A leading wildcard in a LIKE pattern, such as WHERE name LIKE '%mueller%', is also fundamentally not sargable, because the B-tree index cannot find a meaningful starting position for the search. A trailing wildcard, WHERE name LIKE 'mueller%', is sargable in contrast and can use the index. This step of query optimization is particularly rewarding because a single rewrite, without any new index, often drastically cuts runtime.

Implicit type conversions are another common cause of non-sargable conditions. Comparing a column stored as VARCHAR with a numeric constant without quotes causes some databases to silently convert every row instead of using the index. Such cases are often visible in the execution plan as a "Filter" instead of an "Index Cond" on the affected column.

-- NOT sargable: function wraps the indexed column, index cannot be used
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

-- SARGABLE: range comparison lets the database use the index on order_date
SELECT * FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

-- NOT sargable: leading wildcard defeats index navigation
SELECT * FROM customers WHERE last_name LIKE '%mueller%';

-- SARGABLE: trailing wildcard allows index range scan
SELECT * FROM customers WHERE last_name LIKE 'mueller%';

4. Step 3: avoid SELECT * and check projections

SELECT * loads every column of a table, regardless of whether the application actually needs it. For wide tables with many columns, especially with large TEXT or BLOB fields, that costs unnecessary network bandwidth and I/O. The second effect is more subtle and often more decisive for query optimization: SELECT * prevents a covering index from being usable, because the database almost inevitably requests columns not contained in the index and therefore has to access the table additionally.

Explicitly listing the columns actually needed is a small effort with a noticeable payoff. Besides the raw data volume, this practice also improves maintainability, because schema changes, such as adding a new column, do not unintentionally introduce extra data into existing queries. For this step of the query optimization checklist, a short look at the application code is usually enough, without touching the database itself.

-- Wasteful: loads every column including large description and image_data
SELECT * FROM products WHERE category_id = 12;

-- Optimized: only the columns the application actually renders
SELECT product_id, product_name, price, stock_quantity
FROM products
WHERE category_id = 12;
-- If (category_id, product_name, price, stock_quantity) exists as a covering
-- index, this second query can be served entirely from the index

5. Step 4: identify the N+1 problem

The N+1 problem is one of the most common causes of poor performance in applications that rely on ORMs or repeated single queries. Instead of a single query with a JOIN, a list of parent objects is loaded (1 query), and then another query for the associated detail data is run for every single object (N queries). With a hundred parent objects, that is 101 database round trips instead of one, each with its own network and parsing overhead.

The most reliable way to spot an N+1 problem is through query logging during a single request. If you see the same query structure with different parameters repeated over and over in the log output, that is a strong indicator. The fix within query optimization is almost always to replace the N individual queries with a single JOIN or a WHERE IN clause using previously collected IDs, which drastically reduces the number of round trips.

This problem rarely arises from pure lack of SQL knowledge, but usually from the convenience of object-oriented data access patterns, where every object loads its relations on demand without keeping the total number of resulting queries in view. A deliberate look at the query pattern as part of query optimization usually uncovers this problem within a few minutes.

-- N+1 pattern: 1 query for parents, then N queries for children
-- SELECT order_id FROM orders WHERE customer_id = 4821;
-- (for each order_id returned) SELECT * FROM order_items WHERE order_id = ?;

-- Fixed with a single JOIN instead of N round trips
SELECT o.order_id, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.customer_id = 4821;

-- Or with a batched IN clause when the parent IDs are already known
SELECT * FROM order_items WHERE order_id IN (9001, 9002, 9003, 9004);

6. Step 5: check statistics and data type compatibility

If the first four steps did not reveal a cause, it is worth checking how up to date the table statistics are. After large data imports, bulk deletions or migrations, stale statistics can systematically mislead the optimizer, even if every index is correctly present. A simple ANALYZE TABLE in MySQL, ANALYZE in PostgreSQL, or UPDATE STATISTICS in SQL Server often fixes this problem surprisingly quickly, without any structural change being needed.

Data type mismatches between join columns are another frequently overlooked point in query optimization. A join between an INT column and a VARCHAR column that logically contain the same values forces the database into implicit conversions on every comparison and thereby often prevents efficient index usage. Such inconsistencies frequently arise from historically grown schemas or from migrating one system into another.

-- MySQL: refresh statistics after a large import
ANALYZE TABLE orders;

-- PostgreSQL: refresh statistics for the query planner
ANALYZE orders;

-- SQL Server: refresh statistics with full scan for accuracy
UPDATE STATISTICS orders WITH FULLSCAN;

-- Type mismatch example: orders.customer_id is INT, customers.ext_id is VARCHAR
-- Implicit cast on every row prevents efficient index usage
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.ext_id;

7. Step 6: rule out locking and blocking as a cause

Sometimes a query is not slow because of a missing index, but because it is waiting on a lock held by another, longer-running transaction. This phenomenon often does not show up in the execution plan, because the plan itself can be efficient while actual execution is blocked by waiting for locks. A look at active locks and waiting sessions therefore belongs in every complete query optimization checklist.

In PostgreSQL, the system view pg_locks combined with pg_stat_activity shows which sessions are waiting on each other. In MySQL, the information schema table INNODB_LOCK_WAITS provides similar information, in SQL Server the dynamic management view sys.dm_tran_locks helps. A typical pattern is a long-running batch transaction that accidentally locks an entire table while short, otherwise fast queries have to wait on it. This cause is often suspected late in query optimization, even though it is surprisingly common in transaction-heavy systems.

8. Step 7: rewrite the query instead of piling on indexes

If all previous steps point to a structural weakness in the query itself, the last step of the checklist is a deliberate rewrite instead of reflexively adding another index. Correlated subqueries can often be reformulated as a JOIN, which gives the optimizer more freedom in choosing the join strategy. OR conditions across different columns can often be rewritten as a UNION of two separately indexable queries, because a single index rarely serves both sides of an OR condition optimally at the same time.

These rewrites require more understanding than simply adding an index, but often deliver considerably more stable results, because they address the actual structure of the problem instead of treating symptoms. The last step of any solid query optimization is therefore to ask: does an index solve the actual problem, or does it merely mask an unsuitable query structure that will become a problem again as data volume grows?

9. The checklist at a glance

The following table summarizes all steps of the query optimization checklist compactly, including the approximate effort and the typical effect of each step.

This order is deliberately sorted by effort: the early steps of query optimization are quick to perform and resolve the majority of cases, while the later steps require deeper understanding but are needed less often.

10. Summary

Systematic query optimization follows a fixed order: first check the execution plan, then make WHERE clauses sargable, remove unnecessary projections, rule out the N+1 problem, refresh statistics, check locking, and only then rewrite the query structurally. This order reflects both the effort and the hit probability of each step, and prevents wasting valuable time on elaborate diagnostics when the actual cause would have been trivial.

The biggest benefit of this checklist is that a developer can solve the large majority of all performance problems independently, without consulting a DBA. Only when all seven steps fail to yield a clear cause, for example with complex concurrency issues or infrastructure bottlenecks, is escalation to specialized database staff actually necessary. This query optimization checklist saves not just time in practice, but also builds a deeper understanding of how queries actually get executed.

11. FAQ: Query Optimization Checklist