2

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('121212')",
        "id":999,
        "name":"prod1",
        "sellers":[
            {
                "seller_id":99,
                "name":"Business 1",
                "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
                    }
                ]
            },
            {
                "seller_id":9,
                "name":"Business 2",
                "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
                    }
                ]
            }
        ]
    },
    {
        "_id":"ObjectId('232323')",
        "id":1000,
        "name":"prod 2",
        "sellers":[
            {
                "seller_id":44,
                "product_id":2,
                "name":"Business 22",
                "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
                    }
                ]
            },
            {
                "seller_id":91,
                "name":"Business 21",
                "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
                    }
                ]
            }
        ]
    },
    {
        "_id":"ObjectId('989798')",
        "id":1001,
        "name":"prod 3",
        "sellers":[
            {
                "seller_id":33,
                "name":"Business 112",
                "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
                    }
                ]
            },
            {
                "seller_id":32,
                "name":"Business 2",
                "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
                    }
                ]
            }
        ]
    }
]

I have added below indexes for my products collection as below,

  1. index on products.id

    { "id":1 }

  2. index on nested array document

    { "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.

Pradipkumar Chavda
  • 59
  • 5
  • Is there something in the provided answer that you believe does not address your question? If so then please comment on the answer to clarify what exactly needs to be addressed that has not. If it does in fact answer the question you asked then please note to [Accept your Answers](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) to the questions you ask – turivishal Mar 04 '21 at 15:03

1 Answers1

0

Your third compound index will show in reject plan, because your condition is different than your compound index,

You are matching seller_id in provides but the provide index is in selles.seller_id

I am not sure what is the right as per your requirement but choose one of below to achieve index in winning plan,

  1. either your query is wrong and you should change it to this,
db.products.find({
    "id":999,
    "sellers":{
        "$elemMatch":{
            "seller_id":30098,
            "providers":{
                "$elemMatch":{
                    "provider_id":517
                }
            }
        }
    }
});
  1. either you have created wrong index and you should correct it to,
{
    "id":1,
    "sellers.providers.seller_id":1,
    "sellers.providers.provider_id":1
}

Second i have noticed that you have created wrong index, i can not see any products key in your documents,

  1. index on nested array document

    { "products.sellers.seller_id":1 }

turivishal
  • 34,368
  • 7
  • 36
  • 59