-1

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?

Bishal Jaiswal
  • 1,684
  • 13
  • 15

2 Answers2

1

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.

Adam Harrison
  • 3,323
  • 2
  • 17
  • 25
0

If you still want to force MongoDB to use your Index, you can fake out the query as follows (I have tested it). In this case I am assuming the values for 'a' in your collection are positive integers only. 

db.Subject.explain("executionStats").find({a:{$gt:-1}, b:1, c:2})

This might have other application functionality implications or if this type of query is a standard pattern create a dedicated index to address the query.

gvphubli.blogspot.com
  • 100
  • 2
  • 13