How to improve mongoDb query performance?

Question

I have a collection named Codes. This is how the Schema is defined:

import mongoose from 'mongoose'
import autoIncrement from 'mongoose-auto-increment';

const Schema = mongoose.Schema;
const CodesSchema = mongoose.Schema(
  {
    configId: { type: Number },
    campaignId: { type: Number },
    permissions: {
      all: { type: Boolean, default: false },
      users: { type: [Number], default: [] }
    }
)

autoIncrement.initialize(mongoose.connection);
CodesSchema.plugin(autoIncrement.plugin, { model: 'Codes', field: 'campaignId' });
export default mongoose.model('Codes', CodesSchema)

There is one query that looks like this:

const query = {
  $and:[
    {$or: [
      {'permissions.all': true},
      {'permissions.users': 12}
    ]},
    {configId: 3}
  ]
};

Codes.find(query, (err, res) => {
  // do something with the result
})

This works fine, but if there is a huge number of documents in the database then this query is really slow.

Is there anything I can do to improve the performance of this specific query? I'm thinking that createIndex would help, but I'm not sure if that can be applied since there are $and and $or conditions.

UPDATE

I've added indexes this way:

CodesSchema.index({configId: 1, 'permissions.all': 1, 'permissions.users': 1});

But running the query with .explain('executionStats') option returns:

{
    "executionSuccess" : true,
    "nReturned" : 6,
    "executionTimeMillis" : 0,
    "totalKeysExamined" : 10,
    "totalDocsExamined" : 10,
}

Which doesn't seems right because the number of docs examined is greater than the number of docs returned.

Is indexFilterSet what I should be looking for? If yes, then it's set to false. — Valip
– Valip, Commented Feb 22, 2019 at 10:27
No, you should be looking for docs.mongodb.com/manual/indexes . And mongoosejs.com/docs/guide.html#indexes I guess. You are using mongoose, aren't you? — Alex Blex
– Alex Blex, Commented Feb 22, 2019 at 10:52
If I set index: true to the configId field then that should be enough for improving the performance of that query? — Valip
– Valip, Commented Feb 22, 2019 at 11:10

Alex Blex · Accepted Answer · 2019-02-22 12:23:47Z

1

The index itself is correct.

It must be CodesSchema.index, not Code.index.

Ensure you call Code.syncIndexes to update indexes dbside.

The "explain" part - you should check winningPlan.

If no indexes are used by the query, it should be something like

    "winningPlan" : {
        "stage" : "COLLSCAN",
        "filter" : {

When the index is being used it changes to

    "winningPlan" : {
        "stage" : "FETCH",
        "inputStage" : {
            "stage" : "OR",
            "inputStages" : [ 
                {
                    "stage" : "IXSCAN",
                    "keyPattern" : {

answered Feb 22, 2019 at 12:23

Alex Blex

37.2k7 gold badges53 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Valip Over a year ago

Inspecting the winningPlan shows that all indexes are used. I've tried to create the indexes one by one (instead of using multi indexes like in the provided example) and the total number of evaluated documents is reduced with almost 50%, but the rejectedPlans returns 1 object ... is that wrong?

Valip Over a year ago

Basically I'm asking if something is wrong when there are rejected plans

Alex Blex Over a year ago

Please read the docs docs.mongodb.com/manual/indexes and understand the difference between simple indexes, compound indexes and covered queries. RejectedPlans is a list of possible queries. The planner tried all of them to pick the most performant one. You will always have them if there is more than one way to fulfil the query.

matthPen Over a year ago

To provide additionnal informations, take care of index size and writing performance with this index : as your permissions.users field is an array, an index entry will be created for each element of array, so your index will grow up fast when inserting data. Moreover, as an index is sorted, adding/removing data in this array will result in an index partial rewrite. Keep it in mind when chosing indexed fields.

Collectives™ on Stack Overflow

How to improve mongoDb query performance?

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related