Hi folks!

The $or Clauses and Indexes describes how indexes work with a very basic example of $or:

db.inventory.find( { $or: [ { quantity: { $lt: 20 } }, { price: 10 } ] } )

And the best strategy for the above is to have 2 separate indexes for each $or clause:

db.inventory.createIndex( { quantity: 1 } )
db.inventory.createIndex( { price: 1 } )

Now if we change the above query to be something like the following, what would be the best indexing strategy?

db.inventory.find( { brand: 'nike', $or: [ { quantity: { $lt: 20 } }, { price: 10 } ] } )

I would assume the following compound indexes would work best:

db.inventory.createIndex( { brand: 1, quantity: 1 } )
db.inventory.createIndex( { brand: 1, price: 1 } )

Now the reason I am asking this question, is because while I have setup the indexes like above, Mongo does not pickup the correct index for one of the clauses, leading to high document scan.

Here are the details simplified.

Indexes:

db.createIndex( { ref: 1, permission: 1 } )
db.createIndex( { mission: 1, permission: 1 } )
db.createIndex( { mission: 1, ref: 1 } )

Query:

{
    mission: ObjectId('67403f86d51c6db1e1ecd97b'),
    $or: [
        {
            ref: ObjectId('64742caafe06f2171069def2'),
        },
        {
            permission: 1,
        },
    ],
}

Explain Visualized:

Screenshot 2025-03-21 2143221052×730 23.4 KB

As you can see the IXSCAN on the left correctly used the { mission: 1, permission: 1 } index, but the one on the right does not pick up the { mission: 1, ref: 1 } index and instead picks up { ref: 1, permission: 1 } .

If I go ahead and delete the { ref: 1, permission: 1 } index, then it picks up the correct index, as seen below.

Screenshot 2025-03-21 2159551043×664 13.1 KB

I would appreciate any recommendations to mitigate the above issue, without having to split the query into two separate queries, which I am considering to do as a fallback.

@steevej As mentioned I can verify your solution works!