mongodb compound index for $or query

Question

For the following query, what indexes should I create

        $match:{
      "from_time": {
        "$gte": "2024-12-17T18:00:00.000Z"
      },
      "to_time": {
        "$lte": "2025-03-17T19:00:00.000Z"
      },
      "count": {
        "$gte": 10
      },
      "$or": [
        {
          "hash": "839236de",
          "client": "615dd91bee22130011c85bd8"
        }
      ]
    }

should I create 1 compound index for this query: from_time:1, to_time:1, count:1, hash:1, client:1

or 2 compound indexes ($or fields as separate ):

from_time:1, to_time:1, count:1 and hash:1, client:1

2 compound indexes. Whichever you choose, use that query with find and explain to verify that the indexes are being used. (Also, I hope you're storing time as actual BSON datetime objects and not strings.) Btw, with an $or like that, that doesn't do what you want. See: mongoplayground.net/p/4wjqrs9be4V (no docs found). Instead, the $or should contain the two blocks of options separately. Like this: mongoplayground.net/p/SsYAFRvCwmp (both docs found). — aneroid
– aneroid, Commented Mar 17 at 19:09
The way you have $or, it's unclear what you were trying to do. The two options are in the same { } object: "$or": [ { "hash": "839236de", "client": 615dd91bee22130011c85bd8" } ] so it could mean those 3 terms OR these 2 terms. If you meant "from_time and to_time and count and (hash OR client)", then it should have been "$or": [ { "hash": "839236de" }, { "client": 615dd91bee22130011c85bd8" } ]. — aneroid
– aneroid, Commented Mar 17 at 19:40
Your current query is basically an AND between all the query terms. See this example with more documents: mongoplayground.net/p/KtOzGtZDtVZ. If you want the logic to be ... AND ("hash" OR "client"), then make the change as per my previous comment and the output is this: mongoplayground.net/p/HSX4Pr6lqW8 — aneroid
– aneroid, Commented Mar 18 at 6:42

user20042973 · Accepted Answer · 2025-03-17 19:58:10Z

0

Edit This answer is written assuming that the intended logic of the query is "from_time andto_timeandcountand(hashOR client)". But that certainly isn't the only possibility. The only thing I can say for sure at this point is that if the query is providing the desired logic then the $or is unnecessary.

---

Two compound indexes (per the docs), but not the two you propose.

What you want is to group the fields together that logically get combined when searching for matching documents. In your case the three top level fields (from_time, to_time, and count) all get paired up with both hash and client. Moreover, you want to place equality condition(s) first in the index definition, see also ESR indexing guidelines.

With those things in mind, your two indexes could be something like:

{ hash: 1, from_time: 1, to_ time: 1, count: 1 }
{ client: 1, from_time: 1, to_ time: 1, count: 1 }

Notably the ordering of the three range predicate fields amongst each other can make a difference, but it's difficult to advise on that without further details about their selectivity.

Agree with the guidance that @aneroid gave in the comment, in particular using .explain() to verify performance and that your $or is currently written incorrectly.

edited Mar 17 at 19:58

answered Mar 17 at 19:31

user20042973

5,2202 gold badges6 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

aneroid Mar 17 at 19:38

Note that the OP's $or query doesn't keep each item separate. They are in the same { } object: "$or": [ { "hash": "839236de", "client": 615dd91bee22130011c85bd8" } ]. If the $or was meant to be the way your answer proposes, then it should have been "$or": [ { "hash": "839236de" }, { "client": 615dd91bee22130011c85bd8" } ]

user20042973 Mar 17 at 19:44

Ah right. I had clearly assumed that's what they intended, but obviously the typo/intent could be something else. If the query is actually written correctly then there is no need for $or at all! I hadn't checked your playground link as I assumed it was making the same change as I was suggested, but of course they could in fact mean to $or all the conditions. I'll add a clarification

Collectives™ on Stack Overflow

mongodb compound index for $or query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related