Mongodb indexes by grouped fields

72 views Asked by At

I've been doing the Mongo DB university course and started to look more at our application indexes; we have a collction that has a common query using the following fields:

_id:string, stats(schema).created_by:string, list(schema).user_id:string, deleted:false

Schema example:

var listSchema = schema({
    user_id: {type: String, trim: true},
    email: {type: String, trim: true}
}, {_id: false, versionKey: false});    

var modelSchema = schema({
   name: {type: String, trim: true, required: true},
   stats: {
          created_by: String,
          updated_at: {type: Date, default: new Date()},
   },
   deleted: {type: Boolean, default: false},
   list: [listSchema],
   other....
})

The most common query looks like this:

var queryParams = {
        _id: projectId,
        $and: [
            {
                $or: [
                    {'stats.created_by': userId},
                    {'list.user_id': userId}
                ]
            }
        ],
        deleted: false
    };
    // Fire the request to find an object based on the above queryParams
    ModelObject.findOne(queryParams....

The question is, what is the best index configuration to use here, the collection is all in one. Is it a matter of creating a group index by the above fields? _id is already indexed, can this be re-used here!!! Does the $and and $or have any impact?

modelSchema.index({'_id': 1, 'stats.created_by': 1, 'list.user_id': 1, 'deleted': 1});

Any advice, best practice would be appreciated here.

Thanks.

J

0

There are 0 answers