Interview Revision
Revision: Data Modeling and Indexes
Embed vs reference, ESR, compound indexes, and explain() output.
- Access pattern first. Loaded together → together in the document.
- Embed. 1:1 / 1:few, owned, bounded —
address,order.items(snapshotunitPrice). - Reference. Unbounded or queried alone —
orders.userId. Array of ids only if capped. - 16MB. No cap on an array → child collection. Multikey = one index key per element.
- Validation.
$jsonSchemaon the collection. Does not migrate old docs.
- Index. B-tree.
_idalready unique. Each extra index costs writes + RAM. - Compound. Left prefix or nothing.
{ userId, createdAt }does not serve{ status }. - ESR. Equality, then sort, then range. Sort + range on the same field → one key.
- Unique. Database constraint;
11000. Null/missing needs partial/sparse. - explain('executionStats'). Examined vs returned. COLLSCAN vs IXSCAN+FETCH. Covered = IXSCAN, no FETCH,
_id: 0. SORT in the plan = index did not provide order.
db.orders.createIndex({ userId: 1, createdAt: -1 })
db.orders.find({ userId }).sort({ createdAt: -1 })
db.users.find({ city: "Bangalore" }).explain("executionStats")Interview question
How do you decide between embedding and referencing?
Embed when data is read together, owned by the parent, and bounded. Reference when it is large, shared, or grows without limit. Line items embed. A user's orders reference. Access pattern first.
Interview question
How does compound index field order work? What is ESR?
Left prefix: { a, b, c } serves a, a+b, a+b+c — not b alone. ESR: equality fields, then sort, then range, so the index can seek and walk in order. A range before the sort field usually forces an in-memory SORT.
Interview question
A query is slow. What do you look at first?
explain('executionStats'): totalDocsExamined and totalKeysExamined versus nReturned. COLLSCAN where I expected IXSCAN means no usable index. A SORT stage means the index did not provide order. Then rewrite, then one ESR index — not index every field.
Practice