M201 Filtering and Sorting - query predicates more than compound index

After reading a few posts in the forum , I am still confused of compound indexes on filtering and sorting.

Query:

db.people.find({
“job”: /^P/,
“first_name”: /^C/,
“address.state”: “Indiana”
}).sort({ “last_name”: 1 })

Index:

{ “address.state”: 1, “last_name”: 1, “job”: 1 }

Why does the index can be used for sorting on the query?
In the query predicate, there are “job” and “first_name” which are not in the compound index.
Will they block the Index scan(index prefix) and query plan use “collection scan” in the first stage?

If not, does it mean that the query planner does not concern those fields which are not in the indexes when counting the index prefix?

so, the following query still utilizes the compound index since the index prefix are “address.state”(filtering) > “last_name”(sorting)?

db.people.find({
“job”:{ $gte:“P” },
“first_name”: {$lte:“C”},
“address.state”: “Indiana”
}).sort({ “last_name”: 1 })

Very good reading at Performance Best Practices: Indexing | MongoDB Blog and the equality-sort-range is very important.

We have equality on address.state, sort on last_name and range on job.

That’s kind of what is happening. When we are in the index sub-tree of state:Indiana we have all the last_name already sorted.

Yes. Equality-Sort-Range

1 Like