db.col.createIndex( { A: 1, B: -1 } )

Will this query work:

db.col.find( { A: { $gt: 5 }, B: { $gt: 10 } } )

The sort order of be in query is the opposite of the sort order of the index. Why does this make a difference, if it does?

CodePudding user response：

After rereading the question a few times, I think the answer to the question is:

No, the fact that the B key is defined in descending order does not make a difference in the database's ability to use it for the given query

There is some potential nuance here though.

Index Usage

As with your other question, we can have a look at explain output to take a look at what is going on here. As described in the question, the index and the query produces an explain plan that includes the following for the index scan stage:

        "direction": "forward",
        "indexBounds": {
          "A": [
            "(5.0, inf.0]"
          ],
          "B": [
            "[inf.0, 10.0)"
          ]
        },

If we change the index so that B is in ascending order, then we instead get the following:

        "direction": "forward",
        "indexBounds": {
          "A": [
            "(5.0, inf.0]"
          ],
          "B": [
            "(10.0, inf.0]"
          ]
        },

In both cases the database traverses forward through the index. The difference is simply that the index bounds for B are reversed to match the how the database will encounter them based on the structure of the index.

Nuances

In terms of performance, I don't think that there is going to be any difference here. The database should still have to scan the same number of index keys and similarly seek to new positions in the index the same amount of times.

What will make a (potentially large) difference is the ordering of the two keys relative to one another in the index definition. This is due to the fact that the query has range predicates on both of the indexed keys. So depending on the cardinality of the fields in the result set, the index with one key first may perform meaningfully different than having it the other way around. Other possibilities are that the two variants perform similarly or that there are particular values of the query predicates that utilize one better than the other. Somewhat counterintuitively, it is also possible that having only one of the fields in the index would be best which would happen in the situation that the field on the other predicate is not particularly selective (but would force additional repositioning during the index scan).

"The sort order of B in the query"

Probably along the lines of what @Joe mentioned in the comments, the phrase above from the question confused me for a while. There is no sort for this query. Said another way, the query is not requesting that the results be returned in a specific order. The answer above is based on an interpretation that you're curious about a potential discrepancy with usage of the $gt operator with an index key that is in descending order.