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

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

• mongodb
• indexing
• database-performance

• 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/p/4wjqrs9be4V (no docs found). Instead, the $or should contain the two blocks of options separately. Like this: mongoplayground/p/SsYAFRvCwmp (both docs found). – aneroid Commented Mar 17 at 19:09
• 1 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 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/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/p/HSX4Pr6lqW8 – aneroid Commented Mar 18 at 6:42

1 Answer 1

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.