Hi All,
I need help regarding query optimisation for nested array of objects.
We have collection with large number of documents and every single document contains nested array of objects up to 3rd level as below :
Collection name : products
Total documents in collection : 20 million
Size of each document : >= 500 kb
{
_id: ObjectId(""),
id: 999,
name: "prod1",
// all other product fields
sellers:[
{
"_id" : ObjectId(""),
"seller_id" : 99,
"name" : "Business 1",
// all other fields
"providers":[
{
seller_id:99,
provider_id:1,
provider_name:"prov 1"
quantity:50,
order_allowed:40,
notification_on_stock:true
},
{
seller_id:99,
provider_id:2,
provider_name:"prov 2"
quantity:20,
order_allowed:20,
notification_on_stock:true
}
],
// next fields
},{
"_id" : ObjectId(""),
"seller_id" : 9,
"name" : "Business 2",
// all other fields
"providers":[
{
seller_id:9,
provider_id:3,
provider_name:"prov 3"
quantity:50,
order_allowed:40,
notification_on_stock:true
},
{
seller_id:9,
provider_id:4,
provider_name:"prov 4"
quantity:20,
order_allowed:20,
notification_on_stock:true
}
],
// next fields
}
],
},
{
_id: ObjectId(""),
id: 1000,
name: "prod 2",
// all other product fields
sellers:[
{
"_id" : ObjectId(""),
"seller_id" : 44,
"product_id" : 2,
"name" : "Business 22",
// all other fields
"providers":[
{
seller_id:44,
provider_id:3,
provider_name:"prov 3"
quantity:50,
order_allowed:40,
notification_on_stock:true
},
{
seller_id:44,
provider_id:4,
provider_name:"prov 4"
quantity:20,
order_allowed:20,
notification_on_stock:true
}
],
// next fields
},{
"_id" : ObjectId(""),
"seller_id" : 91,
"name" : "Business 21",
// all other fields
"providers":[
{
seller_id:91,
provider_id:1,
provider_name:"prov 1"
quantity:50,
order_allowed:40,
notification_on_stock:true
},
{
seller_id:91,
provider_id:2,
provider_name:"prov 2"
quantity:20,
order_allowed:20,
notification_on_stock:true
}
],
// next fields
}
],
},
{
_id: ObjectId(""),
id: 1001,
name: "prod 3",
// all other product fields
sellers:[
{
"_id" : ObjectId(""),
"seller_id" : 33,
"name" : "Business 112",
// all other fields
"providers":[
{
seller_id:33,
provider_id:1,
provider_name:"prov 1"
quantity:50,
order_allowed:40,
notification_on_stock:true
},
{
seller_id:33,
provider_id:2,
provider_name:"prov 2"
quantity:20,
order_allowed:20,
notification_on_stock:true
}
],
// next fields
},{
"_id" : ObjectId(""),
"seller_id" : 32,
"name" : "Business 2",
// all other fields
"providers":[
{
seller_id:32,
provider_id:1,
provider_name:"prov 1"
quantity:50,
order_allowed:40,
notification_on_stock:true
},
{
seller_id:32,
provider_id:2,
provider_name:"prov 2"
quantity:20,
order_allowed:20,
notification_on_stock:true
}
],
// next fields
}
],
},
I have added below indexes for my products collection as below,
- index on products.id
{
id:1
} - index on nested array document p
{
“products.sellers.seller_id” : 1
} - {
“id” : 1,
“sellers.seller_id” : 1,
“sellers.providers.provider_id” : 1
}
My Query :
db.products.find({
“id”: 999,
“sellers”: { “$elemMatch”: { “providers”: { “$elemMatch”: { “seller_id”: 30098, “provider_id”: 517 } } } }
});
My issue is query always picks up the first index on field id and query took time around 800ms which I need to optimise.