How to use indexes in aggregate

Question

I have created the index using the createIndex and then ensure using ensureIndex

db.getCollection('artists').createIndex( { featured : NumberInt(-1), physicalCount : NumberInt(-1),digitalCount : NumberInt(-1),createdAt: NumberInt(-1) } )

db.getCollection('artists').ensureIndex( { featured : NumberInt(-1), physicalCount : NumberInt(-1),digitalCount : NumberInt(-1),createdAt: NumberInt(-1) } )

which create the index :

"8" : {
    "v" : 1,
    "key" : {
        "featured" : -1,
        "physicalCount" : -1,
        "digitalCount" : -1,
        "createdAt" : -1
    },
    "name" : "featured_-1_physicalCount_-1_digitalCount_-1_createdAt_-1",
    "ns" : "global-rockstar.artists"
}

and now I want to use this index in the aggregate query . I know index not work when we use the $project or $group attribute in aggregate . I want to redesign this query so that it can use the indexes but unable to create one

db.getCollection('artists').aggregate([{
                $match: query <- initially it is {}
            }, {
                $project: {
                    _id: 1,
                    name: 1,
                    genres_music: 1,
                    slug: 1,
                    country: 1,
                    featured: 1,
                    picture: 1,
                    fans: 1,
                    fans_a: 1,
                    credits: 1,
                    totalPlays: 1,
                    createdAt: 1,
                    fanCount: 1,
                    physicalCount: 1,
                    digitalCount: 1,
                    matches: {
                        $add: [{
                            $cond: [{
                                $or: [{
                                    $eq: ['$featured', true]
                                }, {
                                    $eq: ['$featured', 'on']
                                }]
                            },
                                3, 0
                            ]
                        }, {
                            $size: {
                                $setIntersection: ['$genres_music', usergenres]
                            }
                        }]
                    }
                }
            }, {
                $sort: {
                    matches: -1,
                    physicalCount : -1,
                    digitalCount : -1,
                    createdAt: -1
                }
            }, {
                $skip: pageSize * page  <---- pageSize- 15 , page= 0
            }, {
                $limit: parseFloat(pageSize)
            }])

when I am firing the query like

db.getCollection('artists').find({}).sort({
        //"featured" : -1,
        "physicalCount" : -1,
        "digitalCount" : -1,
        "createdAt" : -1
    })

this is giving the unexpected result

Please if any one know how to use the aggregate query with indexes for this query please help me out

Answer 1

According to MongoDB documentation:

The $match and $sort pipeline operators can take advantage of an index when they occur at the beginning of the pipeline.

So If you can, you should put your $sort stage before $project and $group stages.

Placing a $match pipeline stage followed by a $sort stage at the start of the pipeline is logically equivalent to a single query with a sort and can use an index. When possible, place $match operators at the beginning of the pipeline.

In addition, It is preferable to place $skip and $limit stages as early as possible.

Early Filtering If your aggregation operation requires only a subset of the data in a collection, use the $match, $limit, and $skip stages to restrict the documents that enter at the beginning of the pipeline. When placed at the beginning of a pipeline, $match operations use suitable indexes to scan only the matching documents in a collection.

And finally,

For a compound index, MongoDB can use the index to support queries on the index prefixes.