Which of the following index would be optimum for the query? Select all valid. db.test.find( { a : 5, c : 2 })

Question

Options

• Adb.test.ensurelndex( { c:l, a: 1})
• Bdb.test.ensurelndex( { a : 1, c: 1, d: 1, b : 1})
• CCH db.test.ensurelndex( { a :1, c:l})
• Ddb.test.ensurelndex( { a: 1, b :1, c:l, d:l})

Explanation

Options A, B, and C are all valid indexes for this query, with C being the most optimal.

C ({a:1, c:1}) is the ideal index because it covers exactly the two queried fields - no more, no less - giving MongoDB a precise, compact index to satisfy {a:5, c:2} with minimal overhead. B ({a:1, c:1, d:1, b:1}) is also valid because its leftmost prefix {a, c} aligns with both query fields; the extra d and b fields are unnecessary but don't prevent the index from being used. A ({c:1, a:1}) is valid as well - since both fields are equality matches, MongoDB can use this index regardless of field order. D ({a:1, b:1, c:1, d:1}) is the trap: field b sits between a and c, and since b is absent from the query, MongoDB can only use the leftmost prefix {a:1}, meaning it cannot leverage c in the index scan - this results in a less efficient query requiring additional filtering in memory.

Memory tip: Think of compound indexes like a phone book - you can search by last name, or last+first, but you can't skip to "first name only." A gap (an unqueried field) in the index key sequence breaks the chain and stops optimization at that point.

No community discussion yet for this question.