How can I improve the mongodb query containing $elemMatch with $or

35 views
Skip to first unread message

Soorya Prakash

unread,
Jan 17, 2017, 9:45:34 AM1/17/17
to mongodb-user

I am having the document structure below. Here one user holds multiple documents with different continent and country combination array. The country code is of any dataType. I need to fetch the single document for the single user with complete matching the countryarray.

{
 "user" : "Alpha",
 "continent"  : "XXXX"
 "country" : [
                {
                        "countryName" : "AAA",
                        "country_code" : 123
                },
                {
                        "countryName" : "BBB",
                        "country_code" : DDD
                },
                {
                        "countryName" : "CCC",
                        "country_code" : "2"
                },
                {
                        "countryName" : "DDD",
                        "country_code" : X
                }
        ]
}

The below query i tried to execute it.

{
    "user": "Alpha",
    "continent": "XXXX",
    "$and": [{
        "country": {
            "$elemMatch": {
                "$or": [{
                    "countryName": "AAA",
                    "country_code": "123"
                }, {
                    "countryName": "AAA",
                    "country_code": 123
                }]
            }
        }
    }, {
        "country": {
            "$elemMatch": {
                "$or": [{
                    "countryName": "BBB",
                    "country_code": "DDD"
                }]
            }
        }
    }, {
        "country": {
            "$elemMatch": {
                "$or": [{
                    "countryName": "CCC",
                    "country_code": "2"
                }, {
                    "countryName": "CCC",
                    "country_code": 2
                }]
            }
        }
    }, {
        "country": {
            "$elemMatch": {
                "$or": [{
                    "countryName": "DDD",
                    "country_code": "X"
                }]
            }
        }
    }],
    "country": {
        "$size": 4
    }

}

The below is how i indexed my document.

{
                "v" : 1,
                "key" : {
                        "user" : 1,
                        "continent" : 1,
                        "country.countryName" : 1,
                        "country.country_code" : 1
                },
                "name" : "user_1_continent_1_country.countryName_1_country.country_code_1",
                "ns" : "XXX.countries",
                "safe" : true
        }

With this structure and combination , when i do multiple find with create or update the the execution time is very slow.Can anyone help me how i improve my execution speed.

Rhys Campbell

unread,
Jan 17, 2017, 11:59:02 AM1/17/17
to mongodb-user
Include the output of explain for a better answer...


I've often had good results by rewriting $or queries so each is contained within it's own clause. Something like this...

db.data.find({ "$or": [{"user": "Alpha", "continent": "XXXX", "country.countryName": "AAA" }, {"user": "Alpha", "continent": "XXXX", "country_code": "123"}] });

I'd then tailor indexes for each clause...

db.data.createIndex({"user" : 1, "continent" : 1, "country.countryName" : 1});
db.data.createIndex({"user" : 1, "continent" : 1, "country.country_code" : 1});



Reply all
Reply to author
Forward
0 new messages