Read EXPLAIN before adding an index

An index can change how a database locates rows, but it is not a universal cure for a slow query. Inspect the execution strategy, identify the work that matters and verify that the result remains correct before concluding that an index helps.

This walkthrough uses SQLite's EXPLAIN QUERY PLAN. Other databases expose different plan fields and measurement commands. Keep the database engine and version attached to any performance evidence you share.

Reproduce a small, controlled plan change

Download the advanced SQL lab. From its directory run:

python operational_checks.py

The script creates an in-memory database, runs the same parameterized March purchase query before and after adding a date index, checks result equality and writes operational-results.json. It does not modify an external database.

The query shape is:

EXPLAIN QUERY PLAN
SELECT purchase_id
FROM purchases
WHERE purchased_at >= '2026-03-01' AND purchased_at < '2026-04-01'
ORDER BY purchase_id;

The selected purchase IDs must remain P06, P07 and P08. The companion script executes the actual SELECT as well as inspecting its plan, so a changed access path cannot be mistaken for a successful result check.

Read the recorded evidence carefully

On the checked SQLite 3.42.0 environment, the initial plan reports a SCAN using the automatically created purchase primary-key index. That index can provide purchase-ID order, but it does not directly narrow the date interval.

After creating ix_purchases_date on purchased_at, the plan reports a SEARCH using that date index with lower and upper range constraints. It also reports a temporary B-tree for ORDER BY. The new access path narrows the date search but still needs work to produce the requested purchase-ID ordering.

These observations are recorded in operational-results.json. A different SQLite version or dataset can choose a different plan. SQLite explicitly treats this output as diagnostic and warns that its format can change; do not build production logic that depends on exact plan strings. SQLite EXPLAIN QUERY PLAN documentation.

A plan change is not a measured speedup

The fixture has eleven purchases. That is enough to demonstrate access strategies and result invariants, but far too small to support a meaningful production latency claim. No “ten times faster” conclusion follows from the example.

For a real workload, measure representative parameters, realistic data volume and distribution, repeated runs and the expected concurrency. Distinguish cache effects from durable improvements. Record elapsed time and relevant database work, such as rows visited or buffers read, using the engine's tools.

Some explain commands execute the query to collect actual measurements. Check that behavior before using them on modifying statements or expensive production queries. A plan-only command and an execution-measuring command are not interchangeable.

Evaluate the index against the workload

A narrow date interval may benefit differently from a query selecting most of the table. A composite index can help a particular combination of filters and ordering while adding storage and write-maintenance cost. Column order matters; adding every filtered column to an index without considering the access pattern is not a design method.

Inspect joins and aggregation as well. An accidental many-to-many join can create a huge intermediate result even when every input has indexes. Fix the grain and join contract before optimizing the multiplication of incorrect rows.

If estimates and actual cardinalities differ materially in a database that exposes both, investigate data distribution and statistics. Do not force an index merely because a sequential scan looks suspicious; scanning can be sensible when much of the data is needed.

Submit a reviewable optimization

Keep the original query, proposed index, before/after plans, result-equivalence check and representative measurements together. Include the expected effect on writes and a way to remove the index if the workload result is unfavorable.

Exercise: remove ORDER BY and inspect the new plan. Then query the entire January-to-April range. Explain why changing the selected fraction can alter the value of the index even though the SQL is syntactically similar.

NeuraPath's Data Analytics with Generative AI course develops SQL skills that support practical analysis. An optimization portfolio is stronger when it distinguishes a demonstrated plan change from a measured improvement under a real workload.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SQL recursive CTEs for an organization hierarchy.
• Continue with Why a function on a filtered column can slow SQL.