Search code examples
mongodbmongodb-indexes

How does MongoDB compound indexes actually work?

I was creating compound indexes in mongodb, I found a weird behaviour. I created an Index:

db.getCollection('Subject').createIndex({a:1, b:2, c:3})

it created an index named a_1_b_2_c_3.

Now when i am using the mongo find command:

db.getCollection('Subject').find({a:1, b:2, c:3}) //it works fine `a_1_b_2_c_3` is used.
db.getCollection('Subject').find({a:1, b:2}) //this also works fine `a_1_b_2_c_3` is used.
db.getCollection('Subject').find({a:1, c:2}) //this also works fine `a_1_b_2_c_3` is used.
db.getCollection('Subject').find({b:1, c:2}) //But this command doesn't uses the index `a_1_b_2_c_3`.

can anyone let me know why this kind of behaviour is happening?

Solution

  • This is due to index prefixes. The index {a:1, b:1, c:1} has the prefixes { a: 1 } and { a: 1, b: 1}, so the queries that include filters on these fields will use the index.

    Conceptually, think of the index like a B-tree that starts with a filter on a, and has sub-trees on b and c in lower levels. The earlier queries can easily start from the top of the tree and work their way down, whereas the last query ({b:1, c:2}) wouldn't have a simple starting point.