Lab 4.2 Alternative Indexs

MongoDB University M201: MongoDB Performance
#1

Can I use {stars: 1, cuisine: 1}?

It is being treated as an incorrect answer but it still executes fine on my laptop with the following winning plan:

                                    "winningPlan" : {
                                            "stage" : "PROJECTION",
                                            "transformBy" : {
                                                    "cuisine" : 1,
                                                    "stars" : 1,
                                                    "_id" : 0
                                            },
                                            "inputStage" : {
                                                    "stage" : "IXSCAN",
                                                    "keyPattern" : {
                                                            "stars" : 1,
                                                            "cuisine" : 1
                                                    },
                                                    "indexName" : "stars_1_cuisine_1",
                                                    "isMultiKey" : false,
                                                    "multiKeyPaths" : {
                                                            "stars" : [ ],
                                                            "cuisine" : [ ]
                                                    },
                                                    "isUnique" : false,
                                                    "isSparse" : false,
                                                    "isPartial" : false,
                                                    "indexVersion" : 2,
                                                    "direction" : "forward",
                                                    "indexBounds" : {
                                                            "stars" : [
                                                                    "(2.0, inf.0]"
                                                            ],
                                                            "cuisine" : [
                                                                    "[MinKey, MaxKey]"
                                                            ]
                                                    }
                                            }
                                    },

Besides, it does not fetch, which follows the part of the lecture “Covered Queries”. Although I am not sure if it would be quicker since I didn’t figure out how to show the executionStats of aggregation.

#2

@ Extended_54530

No, you cannot successfully use that as an answer. Notice that the setup instructions for the Lab specifically say

Keep in mind that there might be several indexes that resolve this error, but we’re looking for an index as small as possible that services this command.

That index is not the smallest one that will service the command.

That said, in a real production environment, it would very likely be true that the index you propose would be preferable to the “smallest one”, since it would both service this query and probably many others. However, for this Lab, we just want the smallest. Good luck.

2 Likes
#3

oops sorry I did not see that instruction on the smallest. Thank you!

#4

To get the executions statistics:

var c = db.CollectionName ;

c.find(…).sort(…).explain( { statistics : 1 } )
#5

Sorry @Extended_54530, I gave the wrong answer to the correct question.

Here is the correct answer:

db.restaurants.explain( { statistics : 1 } ).aggregate(…)

1 Like
#8

How to get size of individual index?
I used these to arrive at soln
used {explain: true} with aggregate query.Verified fetch and ixscan
Verified totalIndexSize()
23080960
Then checked accesses from indexStats

#9

Hi,

You can get the size of an individual index using collStats. indexSizes.

You can read more about the collstats command in our documentation here:

https://docs.mongodb.com/manual/reference/command/collStats/

Thanks,
Barry

#10

My first attempt was also to use { stars: 1, cuisine: 1 } index. And I’m still convinced that it’s the right solution because: a) in this case winning query plan has IXSCAN stage followed by PROJECTION_COVERED and b) it takes about 6.4s to execute the query on my machine.
On contrary for the { stars: 1 } index winning query plan has IXSCAN stage followed by PROJECTION_SIMPLE stage and takes about 9.6s to execute.
What is the point to use less effective index?
Also I saw a similar thread where someone suggested to use a partial index. Why that solution cannot be accepted?

#11

Please read this thread carefully. Somewhere it is written:

#12

I’ve read and understood what you mean.
But my point is different, it’s not about an answer - it’s about an appropriateness of the question.
In my opinion it does not make sense to ask a question where answer contradicts with a common sense and production requirements. It’s misleading.
It also confusing because covered queries are also part of the chapter so it makes perfect sense to use it in the answer.