M201: Lab 2.2: Different view on Index

MongoDB University M201: MongoDB Performance
#1

Hi, while I could answer the question, I have the following query:
If we have two queries:
Q1 = db.people.find({“address.state”: “Nebraska”,“last_name”: /^G/,“job”: “Police officer”})
Q2 = db.people.find({“job”: /^P/, “first_name”: /^C/, “address.state”: “Indiana”}).sort({ “last_name”: 1 })
There are two indexes:
I1 = { “address.state”: 1, “last_name”: 1, “job”: 1 }
I2 = { “address.state”: 1, “job”: 1 }
And if the selectivity of address.state and job is low and that of address.state and last_name is high, would it not be better to have an index as I2 instead of I1? While I1 would assist sorting, it would happen post filter and hence, should be faster compared to I1. Please share your expert advise.

Thanks,
Sam

#2

Hey @learningsam20

So if not already, at some point in this course you are introduced to the maxim,

Equality, Sort, Range

Which is how to arrange an index upon creation.
A good walk through of this is found here about indexes and sorting.

It is not so much about selectivity as how you query it constructed. For example:

This query has equality match for 'address.state' & job. with a range query on last_name
Therefore this query would use index 2. Since with Index 1 the last_name field ‘gets in the way’ as it is trying to do a range search. However if the query was like below then I1 would be used.

Q1 = db.people.find({
  “address.state”: “Nebraska”,
  “last_name”: "Gordon",
  “job”: /^Po/
})

Try to visualize in your head the index list of documents. For I1 they would be order by state, then matching state by last_name then matching last_name by job.

For:

There is only an equality match on 'address.state' With range queries on both job and first_name while sorting on last_name.
So again I2 would get used to satisfy the query.