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 about various MongoDB indexes which we can perform on our data. I hope you will find this post useful. Thanks for reading.
Learn MongoDB With Me
You can see all the articles on 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.
[
{
"id":2,
"name":"An ice sculpture",
"price":12.50,
"tags":[
"cold",
"ice"
],
"dimensions":{
"length":7.0,
"width":12.0,
"height":9.5
},
"warehouseLocation":{
"latitude":-78.75,
"longitude":20.4
}
},
{
"id":3,
"name":"A blue mouse",
"price":25.50,
"dimensions":{
"length":3.1,
"width":1.0,
"height":1.0
},
"warehouseLocation":{
"latitude":54.4,
"longitude":-32.7
}
},
{
"id":4,
"name":"Keyboard",
"price":15.50,
"dimensions":{
"length":1.1,
"width":1.0,
"height":1.0
},
"warehouseLocation":{
"latitude":24.4,
"longitude":-42.7
}
},
{
"id":5,
"name":"Doll",
"price":10.50,
"dimensions":{
"length":5.1,
"width":1.0,
"height":7.0
},
"warehouseLocation":{
"latitude":64.4,
"longitude":-82.7
}
},
{
"id":6,
"name":"Wallet",
"price":5.50,
"dimensions":{
"length":1.1,
"width":1.0,
"height":1.0
},
"warehouseLocation":{
"latitude":24.4,
"longitude":-12.7
}
}
]
Please be noted that these are just dummy data, and it may sound illogical to you.
C:\Program Files\MongoDB\Server\3.4\bin>mongoimport --db mylearning
--collection products --jsonArray --file products.json
2018-03-06T16:48:34.440+0530 connected to: localhost
2018-03-06T16:48:34.607+0530 imported 5 documents
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 another indexing option that MongoDB has. Sounds 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 right, what a user needs to filter the same with the price and warehouse location. Yeah, we need to build a query.
MongoDB Enterprise > db.products.find({
... "price: {$lte: 16},
2018-03-06T17:10:15.005+0530 E QUERY
[thread1] SyntaxError: unterminated string literal @(shell):2:0
MongoDB Enterprise > db.products.find({
... "price": {$lte: 16},
... "warehouseLocation.latitude": {$gte: 60}
... })
{ "_id" : ObjectId("5a9e790a1ae1f955c1a70c4a"), "id" : 5, "name" : "Doll", "price" : 10.5,
"dimensions" : { "length" : 5.1, "width" : 1, "height" : 7 },
"warehouseLocation" : { "latitude" : 64.4, "longitude" : -82.7 } }
MongoDB Enterprise >
We have got one entry according to our search, "price": {$lte: 16} and "warehouseLocation.latitude": {$gte: 60} that's cool. 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", remember what I 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.
db.products.find({ "price": {$lte: 16}, "warehouseLocation.latitude":
{$gte: 60} }).explain("executionStats")
And if your query if correct, you will be getting a result as follows:
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "mylearning.products",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"price" : {
"$lte" : 16
}
},
{
"warehouseLocation.latitude" : {
"$gte" : 60
}
}
]
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{
"price" : {
"$lte" : 16
}
},
{
"warehouseLocation.latitude" : {
"$gte" : 60
}
}
]
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 107,
"totalKeysExamined" : 0,
"totalDocsExamined" : 5,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{
"price" : {
"$lte" : 16
}
},
{
"warehouseLocation.latitude" : {
"$gte" : 60
}
}
]
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 7,
"advanced" : 1,
"needTime" : 5,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 5
}
},
"serverInfo" : {
"host" : "PC292716",
"port" : 27017,
"version" : "3.4.9",
"gitVersion" : "876ebee8c7dd0e2d992f36a848ff4dc50ee6603e"
},
"ok" : 1
}
You might have already noticed the value we have for totalDocsExamined, 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 is it going to take to fetch the results?
MongoDB Enterprise > db.products.createIndex({price:1, "warehouseLocation.latitude":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
Run your previous query now, and find out what is the value of docs examined.
MongoDB Enterprise > db.products.find({ "price": {$lte: 16},
"warehouseLocation.latitude": {$gte: 60} }).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "mylearning.products",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"price" : {
"$lte" : 16
}
},
{
"warehouseLocation.latitude" : {
"$gte" : 60
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"price" : 1,
"warehouseLocation.latitude" : 1
},
"indexName" : "price_1_warehouseLocation.latitude_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"price" : [ ],
"warehouseLocation.latitude" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"price" : [
"[-inf.0, 16.0]"
],
"warehouseLocation.latitude" : [
"[60.0, inf.0]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 1089,
"totalKeysExamined" : 5,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1,
"executionTimeMillisEstimate" : 310,
"works" : 5,
"advanced" : 1,
"needTime" : 3,
"needYield" : 0,
"saveState" : 2,
"restoreState" : 2,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 270,
"works" : 5,
"advanced" : 1,
"needTime" : 3,
"needYield" : 0,
"saveState" : 2,
"restoreState" : 2,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"price" : 1,
"warehouseLocation.latitude" : 1
},
"indexName" : "price_1_warehouseLocation.latitude_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"price" : [ ],
"warehouseLocation.latitude" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"price" : [
"[-inf.0, 16.0]"
],
"warehouseLocation.latitude" : [
"[60.0, inf.0]"
]
},
"keysExamined" : 5,
"seeks" : 4,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "PC292716",
"port" : 27017,
"version" : "3.4.9",
"gitVersion" : "876ebee8c7dd0e2d992f36a848ff4dc50ee6603e"
},
"ok" : 1
}
MongoDB Enterprise > db.products.find({ "price": {$lte: 16},
"warehouseLocation.latitude": {$gte: 60} })
{ "_id" : ObjectId("5a9e790a1ae1f955c1a70c4a"), "id" : 5, "name" : "Doll",
"price" : 10.5, "dimensions" : { "length" : 5.1, "width" : 1, "height" : 7 },
"warehouseLocation" : { "latitude" : 64.4, "longitude" : -82.7 } }
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 magic 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 a Index. There are so many other complex Indexes, but widely used Indexes are single key index and compound index.
With that, we are done with this post. I will be posting the continuation part of this series very soon. Till then, bye.
Conclusion
Thanks a lot for reading. Did I miss anything that you may think is needed? Could you find this post as useful? I hope you liked this article. Please share 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.
