CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-querydatabase-performance
Valip
Valip

Reputation: 4620

How to improve mongoDb query performance?

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.

Upvotes: 1

Views: 439

Answers (1)

Alex Blex
Alex Blex

Reputation: 37048

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" : {

Upvotes: 1

Related Questions