For this, I'll create a new collection,
customers here and I'll insert many documents. Now
the first customer has a name,
Max, has an age 29 and a salary of 3000, let's say,
the second document Manu has an age of 30 and a salary of 4000, let's say. I inserted these two documents
and now let's add an index with create index of course,
let's create an index on our name here in ascending order like that. Now if I hit enter, I obviously have that
index
and now I can use my customers and explain with execution stats for more information how it would
actually find Max,
so all customers with a name of Max, only one in our case here
but of course it could be more.
So we see that it of course used an index scan for this because we have an index on the name,
we also got no rejected plans right now here,
we see that one element was returned and it looked at one key in the index and returned one document.
Now I said that this could even be zero docs examined
and the question is when could this be zero
because we need to get the document in the end, the index only has a pointer to the document right.
Well the index does not just have the pointer,
the index has one value and that is the well, indexed value,
so Max in this case and Manu,
so the names are in the index, not just the pointers but also the values for the indexed field of course.
And you can reach a so-called covered query if you actually would find Max
and then also add projection here to not return the ID and only return the name and don't return any other
fields,
so essentially what I do here is I only want to return the fields which are also the indexed fields
or the indexed field in this case, name and now you will see that it actually did not examine any documents,
it returned one but it could do that entirely from inside the index.
Now you will not always be able to reach that state but if you can, if you have some query where you
can optimize your index for that, to reach that covered query state as it is called because the query
is fully covered by the index, then you will of course have a very efficient query because you skipped
that stage of reaching out to the collection getting the documents
and that obviously speeds up your query,
if you can get this to work, you will have a very fast solution.
Now of course it does not make sense to build tens of indexes just to cover all possible queries because
then you'll have a problem with writing again and so on
but if you have an opportunity and you have a query that you typically run and you only typically return
these two fields, it might be worth storing them in a single field or if it's two fields, to store them
in a compound index so that you can fully cover the query from inside your index.
And this is something very interesting to know, that you can reach that covered query state sometimes
at least.