Post
Ask Question

Continue With Learning Indexes In MongoDB

Introduction

This is the third article of the series “Learn MongoDB with me”. If you haven’t read my previous post on this topic, I strongly recommend you to find it here. This is the continuation of exploring the Indexes on MongoDB. We will be discussing various MongoDB indexes which we can perform on our data. I hope you will find this post useful. Thanks for reading. You can always read this post on my blog here.

Learn MongoDB with me

You can see all the articles of this series below.

Background

Like I said, it is going to be the third part of the series. I believe that you have enough knowledge about Mongo DB now. If not, please consider reading my previous posts again.

Indexes in MongoDB

Let’s import a new collection, products first.

  1. [{  
  2.     "id": 2,  
  3.     "name": "An ice sculpture",  
  4.     "price": 12.50,  
  5.     "tags": ["cold", "ice"],  
  6.     "dimensions": {  
  7.         "length": 7.0,  
  8.         "width": 12.0,  
  9.         "height": 9.5  
  10.     },  
  11.     "warehouseLocation": {  
  12.         "latitude": -78.75,  
  13.         "longitude": 20.4  
  14.     }  
  15. }, {  
  16.     "id": 3,  
  17.     "name": "A blue mouse",  
  18.     "price": 25.50,  
  19.     "dimensions": {  
  20.         "length": 3.1,  
  21.         "width": 1.0,  
  22.         "height": 1.0  
  23.     },  
  24.     "warehouseLocation": {  
  25.         "latitude": 54.4,  
  26.         "longitude": -32.7  
  27.     }  
  28. }, {  
  29.     "id": 4,  
  30.     "name": "Keyboard",  
  31.     "price": 15.50,  
  32.     "dimensions": {  
  33.         "length": 1.1,  
  34.         "width": 1.0,  
  35.         "height": 1.0  
  36.     },  
  37.     "warehouseLocation": {  
  38.         "latitude": 24.4,  
  39.         "longitude": -42.7  
  40.     }  
  41. }, {  
  42.     "id": 5,  
  43.     "name": "Doll",  
  44.     "price": 10.50,  
  45.     "dimensions": {  
  46.         "length": 5.1,  
  47.         "width": 1.0,  
  48.         "height": 7.0  
  49.     },  
  50.     "warehouseLocation": {  
  51.         "latitude": 64.4,  
  52.         "longitude": -82.7  
  53.     }  
  54. }, {  
  55.     "id": 6,  
  56.     "name": "Wallet",  
  57.     "price": 5.50,  
  58.     "dimensions": {  
  59.         "length": 1.1,  
  60.         "width": 1.0,  
  61.         "height": 1.0  
  62.     },  
  63.     "warehouseLocation": {  
  64.         "latitude": 24.4,  
  65.         "longitude": -12.7  
  66.     }  
  67. }]  

Please note that these are just dummy data, and it may sound illogical to you.

  1. C:\Program Files\MongoDB\Server\3.4\bin>mongoimport --db mylearning --collection products --jsonArray --file products.json  
  2. 2018-03-06T16:48:34.440+0530 connected to: localhost  
  3. 2018-03-06T16:48:34.607+0530 imported 5 documents  
  4. C:\Program Files\MongoDB\Server\3.4\bin>  

If you don’t know how the import command works, please read my previous posts where we have seen simple indexes. Now we have the data, let’s go perform Indexes.

Single Key Indexes

In one of my previous posts in this series of articles, I had mentioned about simple indexes. Here in this article, we are not going to talk about it, instead we will explore other indexing options offered by MongoDB. Sound good? If yes, let’s continue. Let’s go and see Multi key indexes.

Multi Key Indexes or Compound Indexes

As the name implies, we are actually going to set indexes with more than one key element. On our products collection, we have some product documents in which a user needs to filter the same with the price and warehouse location. We need to build a query.

  1. MongoDB Enterprise > db.products.find({ ..."price: {$lte: 16},  
  2.             2018 - 03 - 06 T17: 10: 15.005 + 0530 E QUERY[thread1] SyntaxError: unterminated string literal @(shell): 2: 0  
  3.             MongoDB Enterprise > db.products.find({ ..."price": {  
  4.                     $lte: 16  
  5.                 },  
  6.                 ..."warehouseLocation.latitude": {  
  7.                     $gte: 60  
  8.                 }...  
  9.             }) {  
  10.                 "_id": ObjectId("5a9e790a1ae1f955c1a70c4a"),  
  11.                 "id": 5,  
  12.                 "name": "Doll",  
  13.                 "price": 10.5,  
  14.                 "dimensions": {  
  15.                     "length": 5.1,  
  16.                     "width": 1,  
  17.                     "height": 7  
  18.                 },  
  19.                 "warehouseLocation": {  
  20.                     "latitude": 64.4,  
  21.                     "longitude": -82.7  
  22.                 }  
  23.             }  
  24.             MongoDB Enterprise >  

We have got one entry according to our search, “price”: {$lte: 16} and “warehouseLocation.latitude”: {$gte: 60}  Now let’s try to find out the execution status for the same.

Please note that we have used $lte and $gte which stands for “less than or equal to” and “greater than or equal to”, and remember what I have told you before, “Mongo shell is cool and we can do anything with it”. Let’s find out the examined elements count for our preceding find query now.

  1. db.products.find({  
  2.     "price": {  
  3.         $lte: 16  
  4.     },  
  5.     "warehouseLocation.latitude": {  
  6.         $gte: 60  
  7.     }  
  8. }).explain("executionStats")  

And if your query is correct, you will be getting a result as preceding.

  1. "queryPlanner": {  
  2.     "plannerVersion": 1,  
  3.     "namespace": "mylearning.products",  
  4.     "indexFilterSet": false,  
  5.     "parsedQuery": {  
  6.         "$and": [{  
  7.             "price": {  
  8.                 "$lte": 16  
  9.             }  
  10.         }, {  
  11.             "warehouseLocation.latitude": {  
  12.                 "$gte": 60  
  13.             }  
  14.         }]  
  15.     },  
  16.     "winningPlan": {  
  17.         "stage": "COLLSCAN",  
  18.         "filter": {  
  19.             "$and": [{  
  20.                 "price": {  
  21.                     "$lte": 16  
  22.                 }  
  23.             }, {  
  24.                 "warehouseLocation.latitude": {  
  25.                     "$gte": 60  
  26.                 }  
  27.             }]  
  28.         },  
  29.         "direction": "forward"  
  30.     },  
  31.     "rejectedPlans": []  
  32. }, "executionStats": {  
  33.     "executionSuccess": true,  
  34.     "nReturned": 1,  
  35.     "executionTimeMillis": 107,  
  36.     "totalKeysExamined": 0,  
  37.     "totalDocsExamined": 5,  
  38.     "executionStages": {  
  39.         "stage": "COLLSCAN",  
  40.         "filter": {  
  41.             "$and": [{  
  42.                 "price": {  
  43.                     "$lte": 16  
  44.                 }  
  45.             }, {  
  46.                 "warehouseLocation.latitude": {  
  47.                     "$gte": 60  
  48.                 }  
  49.             }]  
  50.         },  
  51.         "nReturned": 1,  
  52.         "executionTimeMillisEstimate": 0,  
  53.         "works": 7,  
  54.         "advanced": 1,  
  55.         "needTime": 5,  
  56.         "needYield": 0,  
  57.         "saveState": 0,  
  58.         "restoreState": 0,  
  59.         "isEOF": 1,  
  60.         "invalidates": 0,  
  61.         "direction": "forward",  
  62.         "docsExamined": 5  
  63.     }  
  64. }, "serverInfo": {  
  65.     "host": "PC292716",  
  66.     "port": 27017,  
  67.     "version": "3.4.9",  
  68.     "gitVersion": "876ebee8c7dd0e2d992f36a848ff4dc50ee6603e"  
  69. }, "ok": 1  
  70. }  

You might have already noticed the value we have for totalDocsExamined, and if you haven’t please check now. In my case, it is 5, which means the query just examined all the records we have. Ah, that sounds bad, right? What if we have millions of records on our collection, how long it is going to take to fetch the results?

  1. MongoDB Enterprise > db.products.createIndex({  
  2.     price: 1,  
  3.     "warehouseLocation.latitude": 1  
  4. }) {  
  5.     "createdCollectionAutomatically": false,  
  6.     "numIndexesBefore": 1,  
  7.     "numIndexesAfter": 2,  
  8.     "ok": 1  
  9. }  

Run your previous query now, and find out what is the value of docs examined.

  1. MongoDB Enterprise > db.products.find({  
  2.     "price": {  
  3.         $lte: 16  
  4.     },  
  5.     "warehouseLocation.latitude": {  
  6.         $gte: 60  
  7.     }  
  8. }).explain("executionStats") {  
  9.     "queryPlanner": {  
  10.         "plannerVersion": 1,  
  11.         "namespace": "mylearning.products",  
  12.         "indexFilterSet": false,  
  13.         "parsedQuery": {  
  14.             "$and": [{  
  15.                 "price": {  
  16.                     "$lte": 16  
  17.                 }  
  18.             }, {  
  19.                 "warehouseLocation.latitude": {  
  20.                     "$gte": 60  
  21.                 }  
  22.             }]  
  23.         },  
  24.         "winningPlan": {  
  25.             "stage": "FETCH",  
  26.             "inputStage": {  
  27.                 "stage": "IXSCAN",  
  28.                 "keyPattern": {  
  29.                     "price": 1,  
  30.                     "warehouseLocation.latitude": 1  
  31.                 },  
  32.                 "indexName": "price_1_warehouseLocation.latitude_1",  
  33.                 "isMultiKey": false,  
  34.                 "multiKeyPaths": {  
  35.                     "price": [],  
  36.                     "warehouseLocation.latitude": []  
  37.                 },  
  38.                 "isUnique": false,  
  39.                 "isSparse": false,  
  40.                 "isPartial": false,  
  41.                 "indexVersion": 2,  
  42.                 "direction": "forward",  
  43.                 "indexBounds": {  
  44.                     "price": ["[-inf.0, 16.0]"],  
  45.                     "warehouseLocation.latitude": ["[60.0, inf.0]"]  
  46.                 }  
  47.             }  
  48.         },  
  49.         "rejectedPlans": []  
  50.     },  
  51.     "executionStats": {  
  52.         "executionSuccess": true,  
  53.         "nReturned": 1,  
  54.         "executionTimeMillis": 1089,  
  55.         "totalKeysExamined": 5,  
  56.         "totalDocsExamined": 1,  
  57.         "executionStages": {  
  58.             "stage": "FETCH",  
  59.             "nReturned": 1,  
  60.             "executionTimeMillisEstimate": 310,  
  61.             "works": 5,  
  62.             "advanced": 1,  
  63.             "needTime": 3,  
  64.             "needYield": 0,  
  65.             "saveState": 2,  
  66.             "restoreState": 2,  
  67.             "isEOF": 1,  
  68.             "invalidates": 0,  
  69.             "docsExamined": 1,  
  70.             "alreadyHasObj": 0,  
  71.             "inputStage": {  
  72.                 "stage": "IXSCAN",  
  73.                 "nReturned": 1,  
  74.                 "executionTimeMillisEstimate": 270,  
  75.                 "works": 5,  
  76.                 "advanced": 1,  
  77.                 "needTime": 3,  
  78.                 "needYield": 0,  
  79.                 "saveState": 2,  
  80.                 "restoreState": 2,  
  81.                 "isEOF": 1,  
  82.                 "invalidates": 0,  
  83.                 "keyPattern": {  
  84.                     "price": 1,  
  85.                     "warehouseLocation.latitude": 1  
  86.                 },  
  87.                 "indexName": "price_1_warehouseLocation.latitude_1",  
  88.                 "isMultiKey": false,  
  89.                 "multiKeyPaths": {  
  90.                     "price": [],  
  91.                     "warehouseLocation.latitude": []  
  92.                 },  
  93.                 "isUnique": false,  
  94.                 "isSparse": false,  
  95.                 "isPartial": false,  
  96.                 "indexVersion": 2,  
  97.                 "direction": "forward",  
  98.                 "indexBounds": {  
  99.                     "price": ["[-inf.0, 16.0]"],  
  100.                     "warehouseLocation.latitude": ["[60.0, inf.0]"]  
  101.                 },  
  102.                 "keysExamined": 5,  
  103.                 "seeks": 4,  
  104.                 "dupsTested": 0,  
  105.                 "dupsDropped": 0,  
  106.                 "seenInvalidated": 0  
  107.             }  
  108.         }  
  109.     },  
  110.     "serverInfo": {  
  111.         "host": "PC292716",  
  112.         "port": 27017,  
  113.         "version": "3.4.9",  
  114.         "gitVersion": "876ebee8c7dd0e2d992f36a848ff4dc50ee6603e"  
  115.     },  
  116.     "ok": 1  
  117. }  
  118. MongoDB Enterprise > db.products.find({  
  119.     "price": {  
  120.         $lte: 16  
  121.     },  
  122.     "warehouseLocation.latitude": {  
  123.         $gte: 60  
  124.     }  
  125. }) {  
  126.     "_id": ObjectId("5a9e790a1ae1f955c1a70c4a"),  
  127.     "id": 5,  
  128.     "name": "Doll",  
  129.     "price": 10.5,  
  130.     "dimensions": {  
  131.         "length": 5.1,  
  132.         "width": 1,  
  133.         "height": 7  
  134.     },  
  135.     "warehouseLocation": {  
  136.         "latitude": 64.4,  
  137.         "longitude": -82.7  
  138.     }  
  139. }  
  140. MongoDB Enterprise >  

Yeah, we got “docsExamined” : 1 , that’s the way to go. Go create some indexes on your top most queries, you can definitely see some magics over there. You can create up to 64 indexes on a collection in MongoDB, but you may need to create only few, only on your top result queries. What you can do is, whenever you are facing any performance issues on any queries, consider that it needs some tuning and definitely an Index. There are so many other complex Indexes, but widely used Indexes are single key index and compound indexes.

With that, we are done with this post. I will be posting the continuation part of this series very soon. 

Conclusion

Thanks a lot for reading. Did I miss anything that you may think is needed? Did you find this post useful? I hope you liked this article. Please share with me your valuable suggestions and feedback.

Your turn. What do you think?

A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on C# Corner, Code Project, Stack Overflow, Asp.Net Forum instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I can.


Similar Articles