Before this lesson

Understand WHERE, ORDER BY and joins. Be aware that indexes also add write costs.

What you will be able to do

Design an index for a query and separate correctness from performance.

Understand the mechanism

An index adds a lookup structure, trading storage and write maintenance for faster access on some queries. More indexes are not always better, and small tables may not use them.

This query filters by course_id and then orders by minutes and id, suggesting a composite (course_id,minutes,id) index. Column order follows the access pattern and is not interchangeable.

EXPLAIN QUERY PLAN describes the plan instead of returning the query result. Inspect SEARCH, SCAN and temporary sorting. Plan wording varies with SQLite versions and statistics, so it is not a permanent textual contract.

Runnable example

This course uses SQLite 3. Open a fresh in-memory database, for example with sqlite3 :memory:, and run the complete script. Each lesson supplies its own schema and sample data, independent of the website database.

CREATE TABLE sessions(id INTEGER PRIMARY KEY,course_id INTEGER,minutes INTEGER);
INSERT INTO sessions VALUES(1,1,25),(2,1,35),(3,2,20),(4,1,25);
CREATE INDEX idx_sessions_course_minutes ON sessions(course_id,minutes,id);
EXPLAIN QUERY PLAN SELECT id,minutes FROM sessions
 WHERE course_id=1 ORDER BY minutes,id;
SELECT id,minutes FROM sessions WHERE course_id=1 ORDER BY minutes,id;

Trace and verify

The final query returns (1,25), (4,25), (2,35). The preceding plan commonly shows covering-index access, but inspect the actual database output.

Common mistakes and boundaries

Timing four rows cannot establish production performance. Measure representative data volumes and read/write ratios. An index cannot repair incorrect joins or aggregation.

Practice challenge

In a fresh practice database, omit CREATE INDEX and compare the plan and final rows.

Reference solution and reasoning

The final three rows should stay identical. The strategy may change to a table scan and temporary sort, showing that indexes change access cost rather than query meaning.

Lab: design an index around a query

This query filters one course, orders newest-first and takes two records. Separate indexes do not automatically combine into the most useful access path. Compare EXPLAIN QUERY PLAN before and after adding a composite index. A plan explains an access strategy; it is not a benchmark.

CREATE TABLE session(id INTEGER PRIMARY KEY, course_id INTEGER, started TEXT, minutes INTEGER);
INSERT INTO session VALUES
(1,7,'2026-09-01T09:00:00Z',20),
(2,7,'2026-09-02T09:00:00Z',30),
(3,8,'2026-09-03T09:00:00Z',40);
EXPLAIN QUERY PLAN SELECT id, minutes FROM session
WHERE course_id=7 ORDER BY started DESC, id DESC LIMIT 2;
CREATE INDEX session_recent ON session(course_id, started DESC, id DESC);
EXPLAIN QUERY PLAN SELECT id, minutes FROM session
WHERE course_id=7 ORDER BY started DESC, id DESC LIMIT 2;
SELECT id, minutes FROM session
WHERE course_id=7 ORDER BY started DESC, id DESC LIMIT 2;

Expected observations

The result is 2|30 then 1|20. Typical SQLite versions show a scan and temporary sort before indexing, then USING INDEX session_recent. Exact plan text is not a portable cross-version output contract.

Why this happens and where it stops applying

The leading course_id locates the equality range; subsequent columns supply the required order inside it. Timestamps use one fixed-width UTC representation so lexical and chronological order agree. Mixed offsets or formats break that assumption. The index omits minutes, so result retrieval can still require table access. Adding it may cover the query but increases storage and write maintenance. Three rows cannot establish a performance gain; measure representative data and workload.

Extension challenge

Is this index necessarily ideal for a new query filtering started across all courses?

Reasoned solution

No. Without a constraint on the leading course_id, the useful access path can change. Inspect the new plan and measured cost before adding an index beginning with started. A related-looking index name does not prove effective use.

Further reading