Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Name what a composite index sorts by (15 minutes)
State how a composite index on (customer_id, placed_on) orders its entries.
Done when: sorted first by customer_id, then by placed_on within each customer, is correctly described.
Hint
Sorted first by customer_id, then, within each matching customer_id, by placed_on.
Easy 2: Name the leading column (15 minutes)
Name the leading column of a composite index declared as (customer_id, placed_on).
Done when: customer_id is correctly named.
Hint
customer_id.
Easy 3: Name what a covering index avoids (15 minutes)
State what a covering index lets a query avoid reading entirely.
Done when: the table itself, via an index-only scan, is correctly named.
Hint
The table itself; the query runs as an index-only scan.
Medium
Medium 1: Decide if an index helps a given query (25 minutes)
An index exists on (customer_id, placed_on). Decide whether WHERE customer_id = 42 AND placed_on = '2026-01-01' can use this index efficiently, and whether WHERE placed_on = '2026-01-01' alone can.
Done when: the first query is correctly identified as fully using the index, and the second is correctly identified as getting little benefit.
Hint
The combined filter fully uses the index; the placed_on-only filter gets little benefit, since placed_on is not the leading column.
Medium 2: Choose the right column order (25 minutes)
Most queries on orders filter by customer_id alone; a smaller number also add a placed_on filter; almost none filter by placed_on alone. Decide the best column order for a composite index on these two columns.
Done when: (customer_id, placed_on) is chosen, with the reasoning correctly citing customer_id as the more commonly filtered column.
Hint
(customer_id, placed_on), putting the more commonly filtered column first.
Medium 3: Decide if a query qualifies for an index-only scan (30 minutes)
An index exists on (customer_id, placed_on, order_id). Decide whether SELECT order_id FROM orders WHERE customer_id = 42 AND placed_on = '2026-01-01' can run as an index-only scan.
Done when: the query is correctly identified as qualifying, since every selected and filtered column already appears in the index.
Hint
Yes, it qualifies, since order_id, customer_id, and placed_on are all already present in the index.
Hard
Hard 1: Explain why skipping the leading column defeats the index (30 minutes)
Explain, in terms of how the index is physically sorted, why a query filtering only on the second column of a composite index cannot use it efficiently.
Done when: the explanation correctly notes the second column's values are only sorted within each leading-column group, not across the whole index, so there is no single contiguous range to search.
Hint
The second column is only sorted inside each leading-column group, so matching values for it are scattered across many separate groups rather than forming one contiguous range.
Hard 2: Explain why a covering index still has a storage cost (35 minutes)
A covering index includes extra columns beyond what a plain index on the same leading columns would need. Explain what this costs in exchange for avoiding table reads.
Done when: the explanation correctly notes the covering index takes more storage and more write-maintenance work, in Level 4 and 5 terms, than a narrower index would, trading that cost for faster reads on the specific query it targets.
Hint
Extra stored columns increase the index's size and the work needed to keep it updated on every write, a cost accepted in exchange for skipping table reads on the targeted query.
Hard 3: Design an index for a two-pattern workload (40 minutes)
A table is queried both as WHERE customer_id = ? alone and as WHERE customer_id = ? AND placed_on = ? together, with no query ever filtering on placed_on alone. Explain whether one composite index can serve both patterns, and if so, which column order to use.
Done when: the answer correctly states one index on (customer_id, placed_on) serves both patterns, since the leading column alone, or the leading column plus the second column in order, both use the index efficiently.
Hint
Yes, one index on (customer_id, placed_on) serves both patterns, since both queries filter starting from the leading column.