How to add index weight on two id fields on MongoDB?-CodePudding

I have a question regarding Mongo indexes for a $or request on two ObjectID fields.

How can I make sure a search will first look at the first argument of the $or expression and only then if no match looks at the second or should we split it into two requests and add this logic in our code?

I tried to use a compound index with weights but it's only working for text search.

Here is what I've tried :

@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 request I'm trying :

db.test.find( { $or: [ { user_id: ObjectId('624c6bada5b7f846e80af8cb')}, { device_id: ObjectId('624c6bada5b7f846e80af8ca')}]} )

The results :

The indexes :

Thank you!

CodePudding user response：

How can I make sure a search will first look at the first argument of the $or expression and only then if no match looks at the second

You can't because that's not how MongoDB works.

or should we split it into two requests and add this logic in our code?

Yes if you must enforce this then you have to split this into 2 separate queries.

Also I think it's best to clarify how a compound index works:

For a compound index, MongoDB can use the index to support queries on the index prefixes.

This is why your query is not using your compound index, because it does not support the query, from the $or docs:

When using indexes with $or queries, each clause of an $or can use its own index.

Basically { device_id: ObjectId('624c6bada5b7f846e80af8ca')} is executed as a separate query, and as we mentioned a compound index requires the prefix to be part of the query but in this case the prefix does not exist. It's a little more complicated than that as the compound index can be used to support the first query, I recommend you go read about how Mongo chooses which index to use to understand that behavior better.