What is the difference between Single Field Indexes & Compound Indexes?

0-2 years experience

1. 1You need to retrieve orders by customerId quickly. How would you create an index, and why would you choose a single‑field index over a compound one?
2. 2If a query filters on status and sorts by createdAt, what index would you define and what would happen if you only created a single‑field index on status?
3. 3Your team notices a query scanning many documents; you suspect indexing. How would you decide whether a single‑field or a compound index is appropriate?

2-5 years experience

1. 1We have fields userId, productId, and timestamp. A query filters by userId and productId and sorts by timestamp, but current single‑field indexes aren't helping. How would you redesign the indexes and what trade‑offs would you consider?
2. 2During a performance review, a query that filters on city and orders by rating slowed after a schema change. Explain how a compound index could fix it and what pitfalls you must watch for.
3. 3Your monitoring shows higher write latency after adding a compound index. How would you assess whether the index is worth keeping?

5-8 years experience

1. 1Our analytics pipelines group by region then filter by date range. We currently have separate single‑field indexes. Design an indexing strategy using compound indexes to minimize latency, and discuss impact on storage and write throughput.
2. 2We need to support queries that filter on (a) userId alone and (b) userId + status. How would you choose between a single‑field index, a compound index, or both, considering prefix rules and index size?
3. 3Explain how MongoDB uses index bounds for compound indexes and what happens when query predicates omit the prefix fields.

8+ years experience

1. 1Our platform stores billions of documents across sharded clusters. We plan to replace many single‑field indexes with a set of compound indexes to reduce RAM usage. Outline a migration plan that minimizes downtime and ensures query correctness, including testing, build strategies, and rollback.
2. 2Multiple teams have independently added single‑field indexes, leading to index bloat. As a staff engineer, propose a governance model and tooling to evaluate when to replace overlapping single‑field indexes with compound ones, balancing performance, operational cost, and developer autonomy.
3. 3Discuss the long‑term maintenance implications of using compound indexes for evolving query patterns, including how you would monitor index usage and decide when to refactor them.