I need some guidance on creating effective Mongo indexes for a $or query on two ObjectID fields.
Should I prioritize the search to look at the first argument of the $or expression before moving on to the second one, or is it better to split the requests into two and handle the logic in our code?
I attempted to use a compound index with weights but found that it only works for text searches.
Below is my implementation:
@index({ user_id: 1 }, { unique: true, partialFilterExpression: { user_id: { $exists: true } } })
@index({ device_id: 1 }, { partialFilterExpression: { device_id: { $exists: true } } })
@index(
{ user_id: 1, device_id: 1 },
{
weights: {
user_id: 10,
},
partialFilterExpression: { user_id: { $exists: true }, device_id: { $exists: true } },
},
)
@modelOptions({
schemaOptions: {
collection: 'test',
timestamps: {
createdAt: 'created_at',
updatedAt: 'updated_at',
},
},
})
export class Test extends Base {
@prop({ required: false })
public user_id?: ObjectId
@prop({ required: false })
public device_id?: ObjectId
}
The query I'm testing:
db.test.find( { $or: [ { user_id: ObjectId('624c6bada5b7f846e80af8cb')}, { device_id: ObjectId('624c6bada5b7f846e80af8ca')}]} )
The results : https://i.sstatic.net/rx3kT.png
The indexes :
https://i.sstatic.net/YAvjv.png
Your insights are greatly appreciated!