Mongodb $or query optimization index-CodePudding

I have a query. It seems that the index does not work. The query takes more than 7 seconds

My model


const likeSchema = new Schema({

    user: {type: ObjectId, ref: 'User'},
    wind: {type: ObjectId, ref: 'Wind'},
    windUser: {type: ObjectId, ref: 'User'},

    comment: {type: ObjectId, ref: 'Comment'},
    commentUser: {type: ObjectId, ref: 'User'},
    
    type: {type: Number, default: 0},
    read: {type: Boolean, default: false},
    datetime: {type: Date, default: Date.now}

}, {strict: false})


likeSchema.index({windUser: 1, commentUser: 1 })

query criteria


let filter = { $or:[{ windUser: user }, { commentUser: user }] }

Is there a problem with my index? Is this or query, index building correct?

CodePudding user response：

As documented here, you need two indexes:

likeSchema.index({ windUser: 1 })
likeSchema.index({ commentUser: 1 })

Databases are not able to efficiently use a single compound index to process a query with an OR. While the compound index you've mentioned would help with a filter against the windUser field, it is not very helpful for commentUser by itself. Since the operator is a $or rather than a $and the database cannot combine both of the bounds at the same time since it would miss results.

Edit Also worth mentioning that questions like this are best understood by gathering and looking at explain output. You should see that with the current configuration that the database chooses to do a collection scan rather than use the compound index that is currently present.