0

Consider a collection

{ "_id" : { "a" : 1, "b" : 1 }, "c" : 1 }
{ "_id" : { "a" : 2, "b" : 3 }, "c" : 4 }
{ "_id" : { "a" : 3, "b" : 4 }, "c" : 5 }
{ "_id" : { "a" : 4, "b" : 5 }, "c" : 6 }
{ "_id" : { "a" : 5, "b" : 6 }, "c" : 7 }
{ "_id" : { "a" : 1, "b" : 5 }, "c" : 6 }
{ "_id" : { "a" : 1, "b" : -5 }, "c" : 6 }

in such a collection when you issue a query like

db.second.find({ "_id.a" : 1}).explain("executionStats")

The results indicate that it does a full collection scan. Is there a reason why it does not use any index ? It seems like a composite _id does not have a compound index ? Is there a reason why not ?

parapura rajkumar
  • 24,045
  • 1
  • 55
  • 85

1 Answers1

3

The behavior is not specific to the composite primary key, but is related to the index for embedded documents.

Mongo DB makes distinction between querying embedded fields using dot notation and document.

Unique index is created on the embedded document as whole for a composite primary key. Querying embedded fields in the document with dot notation is not going to use index. 

db.second.find({ "_id.a" : 1}) -- Doesn't use index

db.second.find({ "_id":{"a" : 1}) -- Uses index

db.second.find({ "_id":{$eq:{"a" : 1}}}) -- Uses index

Similar rule applies when you were to create the index on embedded fields and query as document.

db.second.find({ "_id.a" : 1}) -- Uses index

db.second.find({ "_id":{"a" : 1}) -- Doesn't use index

More information here & here

Community
  • 1
  • 1
s7vr
  • 73,656
  • 11
  • 106
  • 127
  • `db.second.find({ "_id":{"a" : 1})` might say it uses an index but it does not find prefix matched stuff ,in this case it does not return any results. My question is conceptually a prefix query should work on a composite key but it does not... a why is what I was looking for – parapura rajkumar Jan 12 '17 at 13:09
  • Aren't you missing an "}" on your second example? You have db.second.find({ "_id":{"a" : 1}) and shouldn't it be db.second.find({ "_id":{"a" : 1}}) – BoBoCoding Aug 09 '20 at 20:27