Hot ticket query: compound index order and a blocking sort at 32 MB
An internal ticketing SaaS stores 42 million documents (average 1.1 KB) in one tickets collection on a three-node replica set. The hottest query, run about 400 times per minute, is db.tickets.find({tenantId: "t_88", status: "open", assigneeId: "u_412"}).sort({createdAt: -1}).limit(50). Current indexes are {tenantId: 1}, {status: 1, createdAt: -1} and {assigneeId: 1, createdAt: -1}. That query examines roughly 300,000 documents and at peak intermittently fails with "Sort exceeded memory limit of 33554432 bytes, but did not opt in to external sorting". A second feature filters by tag: find({tenantId: "t_88", tags: {$in: ["billing", "urgent"]}}).sort({priority: -1}).limit(20). Write throughput fell 15% after the last index was added, so the team is reluctant to add more indexes without justification.
1. Design the compound index for the hot query using the ESR rule, justify the field order, and say whether any existing index can be dropped.
Read the worked answer
Recognize the shape. All three predicates are equality matches (tenantId, status, assigneeId). The only non-equality component is the sort on createdAt. So the rule is Equality → Sort → Range, with no range part here.
Index:
db.tickets.createIndex({tenantId: 1, assigneeId: 1, status: 1, createdAt: -1})
The three equality fields can appear in any order among themselves without changing this query's ability to use the index for filtering and sorting; createdAt: -1 must be last and must match the sort direction (or be its exact inverse).
Why tenantId first. In a multi-tenant system every query carries tenantId, so putting it first means the index also serves other tenant-scoped queries as a prefix. Among the remaining two, put the more selective, more reused one next; assigneeId is reused by "my tickets" screens, status is low-cardinality and nearly useless alone.
Why it works. The equality predicates bound one contiguous range of index keys, and createdAt:-1 is the next key component. The planner walks that range in order and stops after 50 entries: totalKeysExamined ≈ 50, nReturned = 50, no blocking sort, and the limit is satisfied early rather than after materializing 300,000 docs.
What to drop. {tenantId:1} is a strict prefix of the new index and is now pure write overhead - drop it after confirming with $indexStats that nothing else relies on it in a way the new prefix does not cover. {status:1, createdAt:-1} is not a prefix of the new index; keep it only if a real query uses it. Verify with db.tickets.aggregate([{$indexStats:{}}]) and drop any index with near-zero accesses.ops - that is the evidence needed to justify the change given the 15% write hit.
2. What exactly does the "Sort exceeded memory limit of 33554432 bytes" error mean, and why is allowDiskUse(true) the wrong fix?
Read the worked answer
33554432 is 32 MB, the default internalQueryMaxBlockingSortMemoryUsageBytes. The message means the winning plan could not obtain sorted order from an index, so it had to read all matching documents into memory, sort them by createdAt, and the working set crossed 32 MB. It is a plan problem (no usable index for the sort), not a capacity problem.
Why allowDiskUse(true) is wrong. It lets the blocking sort spill to disk. The error disappears, but the query still reads 300,000 documents 400 times per minute and now also writes and reads temporary files. You have traded a loud failure for a quiet one that gets monotonically worse as the tenant grows, and the disk spill competes with the WiredTiger cache for the same I/O. On a shared replica-set primary that also degrades unrelated writes.
Right fix. The compound index from the previous task: a sort satisfied by an index never performs a blocking sort and never touches the memory limit.
How to prove it. Compare explain("executionStats") before and after:
- Before:
stage: SORT,totalKeysExamined≈ 300,000,nReturned: 50,memUsagenear the limit. - After:
stage: FETCHoverIXSCAN,totalKeysExamined: 50,nReturned: 50,hasSortStage: false.
The ratio totalKeysExamined / nReturned near 1 is the objective evidence that the index, not disk, fixed the query.
3. For the tag query on {tenantId, tags} sorted by priority descending, explain why adding {tenantId:1, tags:1, priority:-1} will not remove the blocking sort, and what the alternatives are.
Read the worked answer
tags is an array, so an index containing it is multikey. A multikey index cannot provide sorted order for a sort on a different field. Each document appears at multiple index keys (one per tag value), and those entries are ordered by tag value, not by priority; the planner must deduplicate the multiple index entries per document and then blocking-sort by priority. So {tenantId:1, tags:1, priority:-1} still produces a SORT stage - and it also writes one index entry per tag per document, which is a plausible cause of the 15% write-throughput drop.
($in on tags behaves like an equality-style bound set for index range generation, but with multiple values each document can match several branches, so the planner generates multiple ranges plus a dedup. That does not restore sorted order either.)
Alternatives, in order of preference:
- Bound the sort input instead of indexing it. With
{tenantId:1}as a prefix (already present in the new compound index), the candidate set per tenant after the tag filter is usually small; a blocking sort over a few hundred documents is cheap and never approaches 32 MB. Verify withexplainrather than assuming. - Denormalize a sortable scalar. Store a
topPriorityorpriorityBucketfield on the document and sort on that with a non-multikey index - but only if this query is genuinely hot; it adds a write-side field to maintain. - Precompute the answer. Maintain a small per-tenant
priority_queuecollection refreshed on write, and read 20 documents by index with no sort at all.
What not to do: add more multikey compound indexes speculatively. Confirm the sort cost with explain("executionStats") first; the last index bought a write penalty and no query improvement.