Find One Pump’s Readings Without Checking Every Pump

The team asks for P7’s readings within a short time range. The table also contains years of measurements from other assets. Checking every row works, but the work grows with data the question does not need.

An index keeps an additional structure organised by selected values. We will examine what its ordering makes easy, then create a composite index without changing the query’s answer.

Order records by the declared keys, find the leading-key region, then expose the candidate IDs.

A typical B-tree index arranges key values so a search can narrow to a relevant region rather than inspect every table row. Leaf entries connect that region to stored row identities or values, depending on the database and index structure.

The database must still retrieve or verify the requested data. If most rows match, a sequential scan can be cheaper. Small tables may also favour scanning because an index lookup adds overhead.

The existence of an index does not require the optimiser to use it.

For an index on asset_id, recorded_at, rows for one asset are grouped before their times are ordered. Equality on the asset and a time range can therefore narrow a contiguous region.

A condition on a later column alone does not generally provide the same direct leading-prefix lookup. Some engines can use additional strategies such as skip scans, so avoid the absolute claim that later columns can never help. Inspect the chosen plan for the actual database and data.

Choose the index order from recurring predicates and ordering needs, not alphabetical column names.

A direct range on recorded_at lets an ordinary index on that column represent the bounds. Wrapping it in a function can prevent that particular access path unless an appropriate expression index or optimiser transformation applies. This property is often called sargability: whether a predicate can be used as a search argument.

Use a half-open time interval with an inclusive start and exclusive end when that matches the domain. It avoids inventing the last fraction of a second in a day. Our fixture uses consistently formatted date text; mixed offsets or formats require a stronger time representation.

An efficient rewrite must return the same boundary records as the original intended question.

CREATE TABLE readings(id INTEGER PRIMARY KEY,asset_id TEXT NOT NULL,recorded_at TEXT NOT NULL,value REAL NOT NULL);
INSERT INTO readings VALUES(1,'P7','2026-01-01',20),(2,'P7','2026-01-02',24),(3,'P8','2026-01-02',80),(4,'P7','2026-01-03',26),(5,'P8','2026-01-04',85);

CREATE INDEX example_asset_time ON readings(asset_id,recorded_at);
SELECT name FROM pragma_index_info('example_asset_time') ORDER BY seqno;

If an index contains all values needed by a query, an engine may avoid some visits to the base table. Such a covering access path trades additional index size and write maintenance for less reading. The exact columns implicitly available depend on the database and table structure.

Do not append every column to every index. Measure representative queries and inspect whether table lookups are a real cost. Sensitive columns in duplicated storage also remain part of the data-management responsibility.

Covering is a property of an index relative to a query, not a universal property of the index name.

Inserts add index entries, deletes remove them, and updates to indexed values move or replace entries. Indexes consume space and can increase write latency. Several overlapping indexes may provide little extra value while multiplying that cost.

Uniqueness indexes also enforce correctness and cannot be discarded merely because one read benchmark is faster without them. Review constraints and workload together before changing an index set.

Read optimisation and invariant enforcement can share a structure while serving different purposes.

Create the candidate index, confirm that answers remain correct, then inspect EXPLAIN QUERY PLAN in SQLite. Use representative row counts and value distributions when measuring. A four-row exercise is useful for syntax and semantics but cannot establish production speed.

Database statistics help the optimiser estimate selectivity. Keep their maintenance appropriate to the engine and version, and investigate skewed values separately. The next lesson compares planned work and observed work without treating either as a promise.

The challenge tests the index’s declared key order and query results. It does not demand an unstable exact plan string.