Now that we had a look at covered queries,
let's also see when mongodb actually rejects a plan.
Now for that, I'll reuse my customers collection from the last video and remember there we have two indexes, the
standard ID index and our own name index.
Now I'll also add additional compound index on customers with create index and I will add age here
in ascending order and name,
now please note that the order here is important for compound indexes and if I would have put name
first, this index here wouldn't make much sense because then I would have a single field index or
name
even though the name is the first field in a compound index and you learned from left to right, a compound
index can be used well standalone, so each field can be used standalone from left to right.
So in this case, if age comes first, we can also filter just for age and take advantage of this index, for
name,
we can take advantage of this index because name is only the second value is mapped to the respective
ages and only sorted within the age category,
so if you filtered for just name and you didn't have that index, name could not be supported by index.
Here it can because we have a single field index and we also get this compound index for the combination
of age and name or just age.
So now I create this compound index here
and now I have three indexes of course.
Now let's search or let's explain,
so customers explain, let's search for all persons with a name and an age, so name maybe Max and age 30.
If I hit enter, you'll see the winning plan is index scan where I use the compound index
and as I mentioned in the compound index lecture, the order in which you specify your arguments when writing
the query does not matter, our compound index was created with age as the first value and name as a second
and then the query I have name as the first value and age is the second
but here in the query, the order does not matter, it still is able to use that compound index because
we have a simple and condition here
and you can of course reverse that and mongodb does reverse it for us, automatically so to say.
Therefore the winning plan is an index scan using that compound index.
Now what we can see is that we now also have a rejected plan and the rejected plan was to use an
index scan just on the name index, that is the single field name index I created.
Of course it made sense for mongodb to also consider this because we have that name index,
we had a query that included a search for name,
so it made sense to consider that name index
but of course since we have another index which simply fits this query better, this compound index,
mongodb chose to use this one as a winning plan and rejected this one.
Now this is interesting to know,
the question of course is how exactly does mongodb figure out which plan is better? For this, mongodb
uses an approach where it simply
first of all looks for indexes that could help you with the query at hand,
so in our last lecture and example, we had the name single field index and age and name as a compound index.
Since our query, our find method included a look for name, mongodb automatically derived that
both the name single field index and the age name compound index could be helpful.
So it came up with two approaches, for other scenarios you might of course have more approaches,
so let's say we have three approaches, mongodb then simply let's those approaches race against each
other but not for the full dataset
but it sets a certain winning condition, right now that should be one hundred documents.
So it looks who's the first to find 100 documents and of course one of the approaches will be the fastest
to reach that threshold, mongodb will then basically use that approach for the real query
and of course that would be cumbersome if it would have to do this for every find method, for every
query you send to the database because that obviously costs a little bit of performance.
Therefore mongodb caches
this winning plan for this exact query,
so for exactly the query you send with the fields you were looking for and the values for the fields
you were looking for,
so it caches this plan as the winner plan for this kind of query. And for future queries that are looking
exactly equal, it uses this winning plan, for future queries that look different, that use different values
or different keys,
it will of course race again and find a winning plan for that type of query.
Now of course this cache is not there forever,
it is cleared after a certain amount of inserts or a db restart.
To be precise, instead of being stored forever,
this winning plan is removed from cache after you wrote a certain amount of documents to that collection,
currently there should be one thousand,
so after you added that many documents, mongodb says I don't know if the current winning plan will still
win because the collection changed a lot,
so I should reconsider.
The same happens if you rebuild the index, so if you drop it and recreate it for example,
it also gets deleted if you add other indexes because that new index could be better,
so the cache for indexes or winning plans I should say gets cleared when you add new indexes or when
you restart the mongodb server,
then this also gets reset.
So this is how mongodb derives
the winning plan and how it stores it
and it's interesting for you as a developer to regularly check your queries, your find or update or delete
queries and see what mongodb actually does,
if it uses indexes efficiently, if maybe a new index should be added, something you can do on your own
if you own the database or you pass that information to your db administrator
or if you maybe need to adjust the query. Maybe you're always fetching data that you don't really need
and you could get a covered query if you just would project the data you need
which happens to be the data stored in the index.
That is why you as a developer need to know how indexes work because either you need to create them
on your own in your next project on which you work alone or because you can optimize your queries or
tell your administrator to optimize the indexes.
Now with that information gained, let's have a look at the last level of verbosity the explain method offers
and that is that we can add all plans execution as a value here.
Now what this will do is it will give us a bunch of output because here, we get detailed statistics for all
plans,
also the rejected plans. So there we can see in detail how would an index scan on our compound index
perform but then also what would happen if we had that index scan on the name index,
how long would that take,
how many documents would it there consider documents and how many keys would it consider,
how long would it take,
here we have zero because it's so super fast but for larger collections, you would of course see a difference.
And with that, you can get detailed analytics on the different indexes and queries and possible ways
of running your query and therefore you should have all the tools you need to optimize your queries
and your indexes.