An index is an additional access path, not a different definition of the query result. Start with a correct query and a stated workload. Here requests select one team, apply an inclusive lower time bound, order by time and id, and take at most three rows. A composite B-tree index can make that pattern efficient.
Index column order matters. An index on (team, created_at, id) groups equal teams together, then orders their events by time and id. SQLite can locate the team's lower bound and walk forward. The reversed prefix (created_at, team, id) organizes data for a different access pattern.
Follow the data
The required index is non-unique: different events may legitimately have the same team and timestamp. The id supplies a deterministic tie-breaker. Payload is not in the requested index, so retrieving it may still require table lookups. A covering index can trade more storage and write work for fewer table reads; it is a workload choice rather than an automatic improvement.
EXPLAIN QUERY PLAN shows the access strategy selected by the SQLite version running the query. SEARCH usually means an indexed subset is located; SCAN describes a traversal and can still be reasonable for a tiny relation or a broad predicate. Temporary sorting may appear when the chosen access path does not satisfy ORDER BY. Plan text and choices can change with SQLite versions, statistics and data distribution.
The build grades the index name, ordered columns, non-unique property and query result. It does not grade a timing threshold or hardcode an exact plan string. A fast incorrect query fails; a correct index on a tiny test table need not be selected to pass.
Sargable predicates let an engine use a useful key range. Wrapping an indexed column in an expression can prevent direct lookup unless a matching expression index exists. The current schema stores integer time values, so created_at >= :since directly expresses the lower bound. Production date/time formats, collation, selectivity, concurrent writes and statistics need separate investigation in the target database.
Work a complete example
The example includes its own schema and data. Every Run creates a new SQLite database, so changing or rerunning it cannot change your other exercises.
CREATE TABLE events(id INTEGER PRIMARY KEY, team TEXT NOT NULL, created_at INTEGER NOT NULL, payload TEXT NOT NULL);
INSERT INTO events VALUES (5,'a',10,'e'),(1,'a',10,'a'),(3,'a',11,'c'),(4,'a',9,'d'),(2,'b',10,'b');
CREATE INDEX events_lookup ON events(team, created_at, id);
SELECT id, created_at, payload
FROM events
WHERE team = 'a' AND created_at >= 10
ORDER BY created_at, id
LIMIT 3;Diagnose the common failure
An index does not promise output order unless the query includes ORDER BY. A strict greater-than comparison also drops valid events exactly at the lower bound. Do not replace bound parameters with string concatenation; a team name containing a quote is ordinary data, and the fixtures include one.
Try a change before building
Change :since so it equals a populated timestamp and predict which rows remain. Inspect EXPLAIN QUERY PLAN before and after creating the index, but compare query results as well. Then reason about how many index entries must be updated when an event changes teams.
Move into practice
Design a composite B-tree access path and inspect plans without confusing indexes with correctness. Checks test the rules; bug hunts ask you to identify a faulty line before seeing its test evidence. The fill-in keeps most of a working statement, and the build asks for the full contract. Public and hidden fixtures vary inputs, preserve duplicate counts and distinguish NULL from text. SQL results appear as tables; predict-output checks use compact JSON rows, one row per line, with null for SQL NULL.
Use the Watch & read resources for the lecture and primary SQLite reference. Reading or watching alone does not grant mastery. These are SQLite exercises; PostgreSQL, MySQL and SQL Server differ in types, syntax, query plans and concurrency behavior.