Part 3 of 6 · 2 min read
Indexing choices
Think of indexing choices like deciding which drawers of a filing cabinet get a labeled tab. The decision rests on how often each drawer is actually opened.
In plain words: The platform's dominant queries decide which indexes earn their cost. Every page view needs to resolve a subject's slug, like databases, to its row quickly, so subjects.slug needs a unique index, already provided by its UNIQUE constraint. The single most common learner-facing query is "show this learner's progress across every module they have touched," which the progress table's primary key on (learner_id, module_id) already serves well, since that composite key's leftmost column is learner_id. A second common query, "how many learners have completed this specific module," filters by module_id alone, which that same primary key cannot serve efficiently, since module_id is not the leftmost column; this calls for a second, explicit index on module_id by itself.
A worked example. Fetching one learner's full progress list uses the primary key's leftmost learner_id column directly. Counting how many learners finished "Level 5, Module 1" needs the separate module_id index, since the primary key alone cannot serve that filter efficiently.
The key lines.
-- Already covered by the primary key's leftmost column
SELECT * FROM progress WHERE learner_id = 501;
-- Needs its own index, since module_id is not the primary key's leftmost column
CREATE INDEX idx_progress_module ON progress (module_id);
SELECT COUNT(*) FROM progress WHERE module_id = 42 AND status = 'completed';
Leftmost-column fit
Leftmost-column fit is whether a query's filter matches a composite index's or primary key's leftmost column, which determines whether that existing structure can serve the query efficiently without an additional index.
Example: learner_id queries get leftmost-column fit from the progress table's primary key for free; module_id-only queries do not, and need their own index.