In the example shown in the video of ESR the reconfigure the index to resemble ESR which is optimal when running a query. My question is this:
Do you have to write the query .find() to match ESR as well? Meaning, Should the query have been re-written to = Cusine, address.state sort(name: 1)
Well, I’m not actually sure what you’re trying to ask here. If I understand you correctly, you want to know whether you need to re-write a query manually to match the “Equality, Sort, Range” optimization. The answer to that is no, you don’t. The MongoDB query optimization process will rearrange the query automatically to optimize it, so long as that does not compromise the actual query.
As a simple example, suppose I have an index {a: 1, b: 1} on a collection. Then if I write a query find( {b: {$gt: 5} , a: “David”} ) this is logically identical to find( {a: “David”, b: {$gt: 5} } since the equality test will narrow the result down either way so that the index can be used. So in that case the query optimizer will internally redo the query into the best format. Therefore it isn’t necessary for you to manually adjust the query to match the index.
hmmmm. lol then perhaps I am a little confused then. In my other related question, I was asking about the quiz question that was confusing to me because the query index didn’t fit the mold of the query exactly so hence it wouldn’t work and there wouldn’t cover the query to be a covered query.
It makes it confusing on when an index can be used or when the optimizer will switch it around and when there is predicate being used and so forth.
in the case of ESR I would assume the query should read E => S => R
I guess is it mostly an issue when there is a part skipped in an index entirely or in the wrong order is most important?
I am a bit disappointed here, as this kind of issue is one you should be able to figure out for yourself, now that you have completed the course. So I’m not going into a lot of detail here.
Let me sketch out a way for you to test these issues for yourself. Set up a small collection. I used the following data for example.
{ "_id" : ObjectId("5cca5a9a9ffe7de69aa4640d"), "a" : 123, "b" : "one string", "c" : "another one string" }
{ "_id" : ObjectId("5cca5b589ffe7de69aa4640e"), "a" : 234, "b" : "two string", "c" : "yet another one string" }
{ "_id" : ObjectId("5cca5b6f9ffe7de69aa4640f"), "a" : 345, "b" : "three string", "c" : "again another one string" }
{ "_id" : ObjectId("5cca5b7f9ffe7de69aa46410"), "a" : 456, "b" : "four string", "c" : "finally another one string" }
Now set up an index on “a” and “b”. Then run a series of queries using “a”, “b”, and “c” in various combinations, using explain() to see whether the index was used and how it affects the results. (If my memory serves me, your previous post was about covered queries, so also project fields to see how that works.) Good luck.
i want to clarify things up but I don’t think it’s fair to criticize my understanding for something that is explained with a query optimization that will rearrange the query. I am not saying you’re wrong or contesting that in any format. What I am saying is that I am little bit confused about how to look at certain things with indexing and assume the correct answer.
The questions were easy to infer with review of the lessons but the points carried over from 1 part to the next get a little hairy in understanding of how something should be.
For example when it is said, Equality, Sort, Range. I am assuming I will see something in the order of equality sort range in my query. However, you’re saying that isn’t the pure case for E and the R part because the query optimizer will change / switch things around to make it work the best way.
So again, when I’m looking at something that has an index of ERS. right? that’s the index.
the query is ERS. to me that was an incorrect usage of ESR. so I didn’t choose it.
However, when I went to the lesson the index of the example was this… ESR
the query was RES… Now, what you’ve said previously is that the query optimizer will rearrange the E and R so that the equality field fits the index first. That is a little confusing.
To me, looking at again the emphasis of ESR should be on the index creation. Which in the video it was. But then the confusing part is nothing is in order of the index for the query. Essential the query is RES as I stated previously. And when you consider for indexing the predicate and order scenarios it adds a layer of confusion.
It seems as in the case of ESR that is a specialized case that makes the query fit the index and the only thing consistent is there being a sort.
maybe I’m confused, maybe i am thinking of things incorrectly. not the index creation but the query part. But when you think of other parts in other lessons it’s a little bit confusing. I appreciate your help