[Chapter 4][CRUD Optimization]Total Number of IndexKeys Examined

So my understanding of index key examination is that,

If in a compound index a certain field say X is the first index followed by Y,Z,etc… , then if in a find query X is used as range filter. Then all the index keys of that particular index are examined.

Is this correct ?

Hi @Koushik_10617 my understanding is that it will examine only the relevant keys but it may take a tad longer than an equality filter.

Hi @Koushik_10617,

The number of keys examined will be the same if we have single field index {x:1} or compound index {x:1, y:1, z:1}.
However, both single field and compound index will have lesser query execution time compared to collection scan.
So, in the case of compound index, ordering of fields is relevant. The first field should be more selective one and the last one should be least selective. So, make sure your compound index consists of most queried and selective fields.

Please let me know, if you have any questions.

Thanks,
Sonali

Hi @007_jb and @Sonali_Mamgain,

Thank you for your reply.
I am still a little confused on the process of IndexKey Examination.
In a query we know that if equality is used in the query predicate then total IndexKeys examined and execution time is significantly reduced.
But if a range filter is used on the first index of a compound index. All the key’s present in that index need to checked to filter out documents that satisfy the range condition for the value of that index key.
So it’s still unclear if all the keys are examined or does the query optimizer have an algorithm to check only the index keys in that range by processing the range value before examining the IndexKeys.

Hi @Koushik_10617,

If the query criteria can be satisfied by the index, then the number of document examined becomes equal to the number of documents returned.
Suppose I have following documents in my collection:

{ “_id” : ObjectId(“5da489274788449640b92253”), “name” : “A”, “age” : 20 }
{ “_id” : ObjectId(“5da489314788449640b92254”), “name” : “B”, “age” : 3 }
{ “_id” : ObjectId(“5da4894f4788449640b92255”), “name” : “C”, “age” : 15 }
{ “_id” : ObjectId(“5da4895b4788449640b92256”), “name” : “D”, “age” : 60 }
{ “_id” : ObjectId(“5da489654788449640b92257”), “name” : “E”, “age” : 54 }
{ “_id” : ObjectId(“5da489744788449640b92258”), “name” : “F”, “age” : 34 }
{ “_id” : ObjectId(“5da4897e4788449640b92259”), “name” : “G”, “age” : 22 }

Now if I create an index : {"age": 1} and execute the following query:

db.coll2.find({“age”: {"$gt": 20}})

The totalKeysExamined, totalDocsExamined and nReturned will be 4.
The indexes are stored as B-tree data structure and thus when a query does an Index Scan, it does not need to examine all the keys or all the documents.

You can also verify this by running explain method with executionStats in your query as shown below:
db.coll2.find({"age": {"$gt": 20}}).explain("executionStats")

Please let me know, if you have any questions.

Thanks,
Sonali

1 Like