Exercise 1: Single-field index

Create an index for a frequent status filter. Explain the query before and after index creation and compare execution statistics.

Exercise 2: Compound index

Design an index for a customer order-history query sorted newest first. Explain why field order matters.

Exercise 3: Prefix reasoning

Given { status: 1, customerId: 1, createdAt: -1 }, list query patterns that use leading prefixes and identify a suffix-only query that does not align with the prefix rule.

Exercise 4: ESR

Design a candidate index for an equality predicate, a sort, and a range predicate. Explain your field order and compare alternative designs.

Exercise 5: Covered query

Create a narrow query and projection that could be covered by an index. Use explain to verify whether document fetches are avoided.

Exercise 6: Multikey

Create an index on a tags array. Test queries for one tag and for multiple predicates on embedded array objects.

Exercise 7: Unique data

Find duplicate email values before creating a unique index. Decide how normalization and missing values should be handled.

Exercise 8: TTL

Create an expiration index for a temporary collection. Explain why TTL deletion is not an exact timer and why application authorization must still check expiry.

Exercise 9: Partial index

Create a partial index for active users and verify whether queries that do and do not include the partial filter can use it.

Exercise 10: Production review

Inspect a collection's indexes, identify candidates that appear redundant, and propose a safe validation plan before hiding or dropping any index.

Challenge

Create a small workload with multiple query patterns and a write-heavy test. Compare index designs for latency, documents examined, index size, and write overhead. Explain the trade-offs rather than choosing an index based on one metric alone.