So we had a look at our first index and this index actually sped up our query, it sped up our query
for people older than 60,
now something interesting happens if we do run the same query for people older than 20.
If I do run this query, it's still super fast but what we see is that we essentially seem to have no
person in our dataset that is younger than 20,
we'll have to go through the entire index, every single element matches our query and then we fetch documents
for that
and you see that the execution time for this is higher than it was for people older than 60.
Now something interesting happens if we get rid of that index which we can do by reaching out to our
database, to our collection and then running drop index and now here we specify the exact same document we
used for creating it,
so dob.age and then one and now you see that index was removed
and now let's run the same query again or explain it,
now that we have no index. What you'll see is that this now actually is faster, we get execution time of 6 milli
seconds.
Now the reason why this is faster is that we save the step of going for the index,
if you have a query that will return a large portion or the majority of your documents, an index can
actually be slower because you then just have an extra step to go through your almost entire
index list and then you have to go to the collection and get all these documents,
so you then just have an extra step because if you do a full collection scan, it can be faster
and it certainly is if you return all elements but even for the majority it would be faster because
with a full collection scan, you already have all the documents in memory and then an index doesn't offer you
any more because that just is an extra step.
Instead here we got all the documents in memory,
we would have needed to go to the documents anyways to fetch them from the pointers the index gives
us,
so now we already have them and since we need most of them, this is now faster.
So if you have queries that regularly return the majority or all of your documents, an index will not
really help you there,
it might even slow down the execution
and that is important to keep in mind as a first restriction that you need to know when planning your
queries.
If you have a dataset where your queries typically only return fractions, like 10 or 20 percent or lower
than that of the documents, then indexes will almost certainly always speed it up.
If you've got a lot of queries that give you back all the documents or close to all the documents,
indexes can't do that much work for you and logically, that makes sense because the idea of index is to
quickly let you get to a narrow subset of your document list and not to the majority of that.