Query takes too long with index created - MongoDB

Question

I've got around 1M of documents more or less like this one:

{
    "_id" : ObjectId("5461c8f0426f727f16000000")
    "ec" : 9,
    "ai" : 9390448,
    "f" : [ 
        {
            "t" : "ry",
            "v" : 1994
        }, 
        {
            "t" : "g",
            "v" : [ 
                "Crime", 
                "Drama"
            ]
        }, 
        {
            "t" : "ml",
            "v" : "English"
        }, 
        {
            "t" : "k",
            "v" : "movie"
        }, 
        {
            "t" : "rel",
            "v" : true
        }
    ]
}

I have the necessity of making queries like these:

db.items.find(
    {
        $and:[
        {f:{$elemMatch:{t:"ml", v:{$in:["English", "Spanish", "French", "German"]}}}}, 
        {f:{$elemMatch:{t:"rel", v:true}}},
                {f:{$elemMatch:{t:"k", v:"movie"}}},
                {f:{$elemMatch:{t:"ry", v:{$gte:1980}}}},
                {f:{$elemMatch:{t:"g", v:{$in:["Thriller"]}}}},

        ]

        }).sort({ai: -1}).limit(12)

And like this:

db.items.find(
        {
            $and:[
            {f:{$elemMatch:{t:"ml", v:{$in:["English", "Spanish", "French", "German"]}}}}, 
            {f:{$elemMatch:{t:"rel", v:true}}},
                    {f:{$elemMatch:{t:"k", v:"movie"}}},
                    {f:{$elemMatch:{t:"ry", v:{$gte:1980}}}},
                    {f:{$elemMatch:{t:"g", v:{$in:["Thriller"]}}}},

            ]

            }).sort({ec: -1}).limit(12)

Note the sorting difference.

I created the following index:

{
    "f.t" : 1,
    "f.v" : 1,
    "ec" : -1,
    "ai" : -1
}

Therefore, I thought I could query including always ec. Even if I just wanted to sort with ai I could introduce {ec: {$gte: 0}} (ec has 0 or greater value for ALL documents).

If I make the following query, it uses the index and everything is ok:

db.items.find(
    {
        $and:[
        {f:{$elemMatch:{t:"ml", v:{$in:["English", "Spanish", "French", "German"]}}}}, 
        {f:{$elemMatch:{t:"rel", v:true}}},
                {f:{$elemMatch:{t:"k", v:"movie"}}},
                {f:{$elemMatch:{t:"ry", v:{$gte:1980}}}},
                {f:{$elemMatch:{t:"g", v:{$in:["Thriller"]}}}},
                {ec: {$gte: 0}}
        ]

        }).limit(12)

However, when I introduce sorting with ai it gets tremendously slow and 'never' comes back:

db.items.find(
    {
        $and:[
        {f:{$elemMatch:{t:"ml", v:{$in:["English", "Spanish", "French", "German"]}}}}, 
        {f:{$elemMatch:{t:"rel", v:true}}},
                {f:{$elemMatch:{t:"k", v:"movie"}}},
                {f:{$elemMatch:{t:"ry", v:{$gte:1980}}}},
                {f:{$elemMatch:{t:"g", v:{$in:["Thriller"]}}}},
                {ec: {$gte: 0}}
        ]

        }).sort({ai: -1}).limit(12)

I don't quite understand this behavior. I'm guessing the only way is to create two different indexes for this.

Ideas?

Thanks.

Answer 1

The documentation states:

An index can support sort operations on a non-prefix subset of the index key pattern. To do so, the query must include equality conditions on all the prefix keys that precede the sort keys.

AFAIK, $gte doesn't count as an equality condition; only { <field>: <value> } does.

Using multiple indexes should fix your problem:

{ "f.t" : 1, "f.v" : 1, "ec" : -1 }
{ "ai" : -1 }

(untested)