CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Create a single-column index (15 minutes)

Write an index on books.author_id.

Done when: a correct CREATE INDEX ... ON books (author_id); is given.

Hint

CREATE INDEX idx_books_author ON books (author_id);

Easy 2: Does this query use the index (15 minutes)

Given an index on (customer_id, placed_on), state whether WHERE customer_id = 107 can use it.

Done when: yes is correctly given, since customer_id is the leading column.

Hint

Yes, since customer_id is the leading (first) column of the composite index.

Easy 3: Does this one (15 minutes)

Given the same index, state whether WHERE placed_on = '2026-01-01' alone can use it well.

Done when: no is correctly given, since placed_on is not the leading column.

Hint

No, since the index cannot be used efficiently starting from its second column alone.

Medium

Medium 1: Composite index column order (25 minutes)

Given that queries almost always filter by customer_id alone, and occasionally also by placed_on, decide the correct column order for a composite index on orders.

Done when: (customer_id, placed_on) is correctly chosen, with customer_id first since it's filtered on more often and alone.

Hint

(customer_id, placed_on), with customer_id first since every relevant query includes it.

Medium 2: Worth indexing or not (25 minutes)

Decide whether books.title, frequently searched by customers, and books.isbn_format, a rarely-queried fixed-value column, are each worth indexing.

Done when: title is correctly judged likely worth indexing, and the rarely-queried column judged likely not.

Hint

title is likely worth indexing given frequent searches; the rarely-queried fixed-value column is likely not worth it.

Medium 3: Two single-column indexes versus one composite (25 minutes)

Explain why a query filtering on customer_id AND placed_on together often benefits more from one composite index on both columns than from two separate single-column indexes.

Done when: the explanation correctly notes a composite index can satisfy both conditions in one lookup, while two separate indexes typically still need combining afterward.

Hint

A composite index lets the database satisfy both filter conditions in a single index lookup, while combining two separate single-column indexes usually needs extra work to intersect their results.

Hard

Hard 1: An index that doesn't help (30 minutes)

A query uses WHERE UPPER(full_name) = 'ASHA RAO' against a plain index on full_name. Explain why this index likely does not speed up this particular query.

Done when: the explanation correctly notes the index is built on the raw column, not on the result of UPPER(), so the database cannot use it directly for this condition.

Hint

The index stores the raw full_name values, not their uppercased form, so a condition wrapping the column in a function typically cannot use that plain index.

Hard 2: Index on a small table (35 minutes)

A tiers table has 5 rows and is queried constantly. Explain why adding an index to it is unlikely to measurably help, even given the frequent queries.

Done when: the explanation correctly notes that scanning 5 rows is already fast enough that an index adds negligible benefit, and some unnecessary maintenance cost.

Hint

Scanning 5 rows directly is already extremely fast, so an index's lookup overhead and its write-time maintenance cost provide little to no real benefit here.

Hard 3: Choosing between two possible leading columns (40 minutes)

Orders are filtered by customer_id in 80 percent of queries and by placed_on alone in the remaining 20 percent. Decide whether one composite index, or two separate single-column indexes, better serves this real mix of queries, and justify the choice.

Done when: the reasoning correctly favors two separate indexes here, since a single composite index on (customer_id, placed_on) would not help the 20 percent of queries filtering on placed_on alone.

Hint

Two separate single-column indexes serve both query patterns well; a single composite index on (customer_id, placed_on) would leave the 20 percent of placed_on-only queries unable to use it effectively.