MongoDB Embeded Index

Question

we have a collection in below format:

{ "_id" : { "a" : 8, "b" : ObjectId("5b23aa9e24826b82a2553ce7") }, "field1" : "value1", "field2" : "value2", "field3" : "value3", "field4" : "value4", }

as per my knowledge, MongoDB creates a default index on _id field. But here in this case I want to know how MongoDB will create index on _id; is it on both _id.a and _id.b field or just on _id.b field? also if I create an additional compound index on (_id.a,field1,field2), will it create an additional overhead? or I need to create and index on only (filed1,filed2) as _id is already indexed field.

Please suggest. Thanks!

Answer 1

But here in this case I want to know how MongoDB will create index on _id; is it on both _id.a and _id.b field or just on _id.b field?

The index will index the embedded document as a whole; i.e. the entirety of { "a" : 8, "b" : ObjectId(...) }. This is a different index entirely than one that would be built solely on _id.a, _id.b, or a compound index on both fields.

Note that querying on a nested document requires an exact match of the specified document. To illustrate:

> db.embed.insert({ "_id" : { "a" : 1, "b" : 2 } } )
WriteResult({ "nInserted" : 1 })
> db.embed.find( { "_id" : { "b" : 2, "a" : 1 } } )
> 
> db.embed.find( { "_id" : { "a" : 1, "b" : 2 } } )
{ "_id" : { "a" : 1, "b" : 2 } }

If a single query will be filtering on _id.a, field1 and field2 (e.g. db.foo.find( { "_id.a" : 8, "field1" : "value1", "field2" : "value2" }) ) then then I would recommend building a compound index on the three fields, i.e. { "_id.a" : 1, "field1" : 1, "field2" : 1 }.

---

Update with more info for comment:

The two queries are not functionally equivalent; the 2nd query will only filter on { "_id": {$lt:{"b":5} }, "field1": "value1" }. For example, try the following:

db.foo.insert({ "_id" : { "a" : 1, "b" : 1 } } )
db.foo.insert({ "_id" : { "a" : 2, "b" : 2 } } )
db.foo.insert({ "_id" : { "a" : 2, "b" : 1 } } )
db.foo.insert({ "_id" : { "a" : 1, "b" : 2 } } )
db.foo.find({ 
 "_id.a":{ "$gt" : 1 },
 "_id.b":{ "$lt": 2} 
})
db.foo.find({
 "_id": { "$gt" : { "a": 1 } },
 "_id": { "$lt" : { "b": 2 } } 
})

Note that queries that filter on multiple fields have an implicit logical AND as long as the fields being filtered on are different. The second query will effectively only be filtering on "_id": { "$lt" : { "b": 2 } } as there are two filters on _id. The $and filter would be needed for the logical AND to be used as intended in this query.

That being said, these two queries are asking different things. The first query is filtering on the embedded values "_id.a" and "_id.b":

> db.foo.find({
... "_id.a" : { "$lt" : 2 }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 1, "b" : 2 } }
>
> db.foo.find({
... "_id.b" : { "$lt" : 2 }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 2, "b" : 1 } }

Whereas the second query is asking to return documents where _the entire value of id is greater than / less than the value specified. This can return unexpected results:

> db.foo.find({
... "_id": { "$lt" : { "a": 2 } }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 1, "b" : 2 } }
>
> db.foo.find({
... "_id": { "$lt" : { "b": 2 } }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 1, "b" : 2 } }
{ "_id" : { "a" : 2, "b" : 1 } }
{ "_id" : { "a" : 2, "b" : 2 } }
> db.foo.find({
... "_id": { "$gt" : { "b": 1 } }
... })
>

• SaP: Thanks @Adam Harrison! on similar lines, if I execute queries like: -->db.foo.find({"_id.a":{$gt:1},"_id.b":{$lt:5},"field1" : "value1"}) and -->db.foo.find({"_id":{$gt:{"a" : 1}},"_id":{$lt:{"b":5}},"field1":"value1"}) both these queries gives same output but execution plan seems to be different, Can you please tell what is the difference between these two queries.
• Adam Harrison: Good question. I just updated my response above to answer this and provide some examples.