Query with $or clauses does not pickup optimal index

mikespy · 2025-03-22T02:02:22.257Z

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 · 2025-03-24T12:05:21.293Z

I would try to move the exact match of mission into each clause.

The query would then look like:

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

steevej · 2025-04-01T12:23:10.463Z

@mikespy, you asked for ideas.

I gave one more than a week ago.

I would appreciate if you could follow-up.

Thanks

mikespy · 2025-04-02T21:07:17.296Z

Thanks @steevej ! I haven’t tested your exact solution but I have no doubt that it works! I will verify and get back to this forum to confirm. The only caveat with the above is that a big payload might be repeated. For simplicity I use a single mission ID, but in reality I am utilizing a list of missions and some of the queries have complex $and that would need to be repeated on each $or clause.

But definitely this is a great option to utilize. I am still curious though why the expected behaviour is not supported. Which is basically picking up the correct index for subsequent $or clauses, not just the first one.

mikespy · 2025-04-07T20:59:14.751Z

@steevej As mentioned I can verify your solution works!