In MongoDB, I am using a large query, how I will create compound index or single index, So My response time boost up

Question

In My Project, I have a post collection and this is the scheme:

posts:{
        title:            {type: String, required: false},
        text:             {type: String, required: false},
        plainDescription: {type: String, required: false},
        isBlog:           {type: Boolean,default: false},
        type:             {type: String, required: true, default:'USER', enum:["USER","GROUP"]},
        information:{
            tags:[],
            users:[],
            groups:[],
            likes:[],
            likesCount:   {type: Number, default: 0},           
            dislikes:[],
            dislikesCount:{type: Number, default: 0},
            spams:[],
            spamsCount:   {type: Number, default: 0},
            shares:[],
            sharesCount:  {type: Number, default: 0},
            comments:[],
            commentsCount:{type: Number, default: 0},
            reply:[],
            favorite: [],
            favoriteCount:{type: Number, default: 0},
            replyCount:   {type: Number, default: 0}
        },
        authorId            : { type: String, required: true},// Can be user Id or Group Id
        authorName          : { type: String, required: false},
        isReply             : { type: Boolean, default: false},
        replyOf             : { type: String, default: "",nullable: true, references: 'posts._id'},
        isQuoteReyell       : { type: Boolean, default: false},
        quoteReyellOf       : { type: String, default: "",nullable: true, references: 'posts._id'},
        createdAt           : { type: Date, default: Date.now},
        createdBy           : { type: String, required: false, references: 'users._id'},
        creatorName         : { type: String, required: true, references: 'users.userName'},
        updatedAt           : { type: Date, required: false,default: Date.now},
        updatedBy           : { type: String, required: false, references: 'users._id'},
        updatorName         : { type: String, required: false, references: 'users.userName'},
        publishedAt         : { type: Date, required: false},
        isBlockedByAdmin    : {type:  Boolean,default: false},
        isDelete            : {type:  Boolean,default: false},
        isRss               : {type:  Boolean,default: false},
        updatedObj          : [],
        sportName           : {type: String, default: ''},
        teamObj             : []
    },

and As per my requirement, this is the query:

[{"$match":{"$and":[{"$or":[{"$and":[{"createdBy":{"$in":["5754c557bd961f3751ddd830"]}},{"createdAt":{"$lt":"2017-10-26T06:17:32.533Z"}},{"information.shares":{"$elemMatch":{"userId":{"$in":["5754c557bd961f3751ddd830"]},"sharedAt":{"$not":{"$gt":"2017-10-26T06:17:32.533Z"}}}}}]},{"$and":[{"isQuoteReyell":{"$ne":true}},{"updatedObj":{"$elemMatch":{"userId":{"$in":["5754c557bd961f3751ddd830"]},"type":"reyell","updatedAt":{"$not":{"$gt":"2017-10-26T06:17:32.533Z"}}}}},{"isReply":false}]},{"$and":[{"information.shares":{"$elemMatch":{"userId":{"$in":["5754c557bd961f3751ddd830"]},"sharedAt":{"$not":{"$gt":"2017-10-26T06:17:32.533Z"}}}}}]},{"$and":[{"isReply":true},{"createdBy":{"$in":["5754c557bd961f3751ddd830"]}},{"updatedAt":{"$lt":"2017-10-26T06:17:32.533Z"}}]},{"$and":[{"updatedObj":{"$elemMatch":{"userId":{"$in":["5754c557bd961f3751ddd830"]},"type":{"$ne":"reyell"},"updatedAt":{"$not":{"$gt":"2017-10-26T06:17:32.533Z"}}}}}]},{"$and":[{"createdBy":{"$in":["5754c557bd961f3751ddd830"]}},{"createdAt":{"$lt":"2017-10-26T06:17:32.533Z"}},{"isBlog":false},{"information.shares":{"$elemMatch":{"userId":{"$in":["5754c557bd961f3751ddd830"]},"sharedAt":{"$not":{"$gt":"2017-10-26T06:17:32.533Z"}}}}}]},{"$and":[{"isQuoteReyell":{"$ne":true}},{"updatedObj":{"$elemMatch":{"userId":{"$in":["5754c557bd961f3751ddd830"]},"type":"reyell","updatedAt":{"$not":{"$gt":"2017-10-26T06:17:32.533Z"}}}}},{"isReply":false}]},{"information.shares":{"$elemMatch":{"userId":{"$in":["5754c557bd961f3751ddd830"]},"sharedAt":{"$not":{"$gt":"2017-10-26T06:17:32.533Z"}}}}},{"$and":[{"isReply":true},{"createdBy":{"$in":["5754c557bd961f3751ddd830"]}},{"updatedAt":{"$lt":"2017-10-26T06:17:32.533Z"}},{"information.shares":{"$elemMatch":{"userId":{"$in":["5754c557bd961f3751ddd830"]},"sharedAt":{"$not":{"$gt":"2017-10-26T06:17:32.533Z"}}}}}]},{"$and":[{"information.shares":{"$elemMatch":{"userId":{"$in":["5754c557bd961f3751ddd830"]},"sharedAt":{"$not":{"$gt":"2017-10-26T06:17:32.533Z"}}}}},{"updatedObj":{"$elemMatch":{"userId":{"$in":["5754c557bd961f3751ddd830"]},"type":{"$ne":"reyell"},"updatedAt":{"$not":{"$gt":"2017-10-26T06:17:32.533Z"}}}}}]}]},{"isBlockedByAdmin":false},{"isDelete":false}]}},{"$unwind":"$updatedObj"},{"$match":{"$or":[{"$and":[{"updatedObj.type":"reyell"},{"updatedObj.userId":{"$in":["5754c557bd961f3751ddd830"]}}]},{"$and":[{"updatedObj.type":"create"}]}]}},{"$group":{"_id":"$_id","updated":{"$last":"$updatedObj"},"post":{"$last":"$$ROOT"}}},{"$sort":{"updated.updatedAt":-1}},{"$limit":15}]

Please help me, For which column, I shouldd create an index or compound index, So it can boost up the response time.

dnickless dnickless · Accepted Answer · 2017-10-26T07:21:36

In general, you will want to put an index on the fields used most as filter criteria in your most important/frequent queries, starting with the most selective fields first. There is quite some decent guidance on the topic as part of the MongoDB documentation. One statement of particular interest in there for your case is probably this since you have a lot of $ors:

Generally, MongoDB only uses one index to fulfill most queries. However, each clause of an $or query may use a different index, and starting in 2.6, MongoDB can use an intersection of multiple indexes.

The most important thing here, however, is to measure, measure, measure and look at query execution plans using explain(). The reason being that most likely you will have different kinds of queries that your application needs to support and you will need to go for a trade-off at some point where you have to choose between index maintenance costs (e.g. write locks during index updates & disk space requirements) and the theoretically fastest solution where all fields used in a single query are covered by a single index.

That whole indexing topic is a bit of a fuzzy one which heavily depends on your precise scenario:

Is your data heavily updated and do writes need to be super-fast (you'll want less/smaller indexes) or is your data pretty stable with frequent reads that have to be fast (go with more/bigger indexes)?
What kinds of queries do you need to support? How similar are they in terms of their filters? Will certain combinations of filters be more likely than others? Which queries need to perform well, which ones can be a bit slower?
How is the data in your potentially indexed fields distributed?
and so on...

You will not find the single index that helps all your queries to perform the best. And also, when adding more indexes or changing existing ones, this can cause the query optimizer to stop using some index for some queries and choose a different execution plan instead which may or may not be desired. So measure everything that's important upon any change to your indexing or physical data layout (hardware setup, sharding...). Lastly, you should measure your query performance on a regular basis as your amount of data grows unless it's predictably uniform in it's distribution.

To cut a long story short: Go for an iterative approach and start by adding an index (I would suggest to add one on isBlockedByAdmin, isDelete and information.shares.userId) then measure your query performance and then refine your index based on your findings (and again, and again, ...).

In MongoDB, I am using a large query, how I will create compound index or single index, So My response time boost up

1 Answers