So let's now have a look at this index thing in practice and understand the impact they have and how we
can create indexes. For this, you should have a running mongodb server,
I have them in a second tab and then you find a starting dataset attached to this video,
the persons.json file. Download that file and store it somewhere on your machine and then
navigate in the terminal or command prompt into that folder where you have it stored so that you can
easily import it with mongo import.
So simply type mongo import,
then the path to that file
and now since I'm running this in the folder where the file is,
I can just type the file name. Then the database where you want to store this and I'll simply create
a new one, contact data and the collection which you want to create for that data and I'll name mine
contacts.
Now you also need to add json array at the end so that this gets imported correctly and it should import
5000 documents.
Now as you connect to your database thereafter, you should have that new contact data database which
we can use now,
so contact data and in there, you should have this contacts collection.
So let's now use that contacts collection and
let's first of all have a look at a single contact, for this I'll reach out to my contacts and find one
element.
So this is essentially what we have in there,
this is some random person data,
each person has an ID, gender, a name which is actually a nested document as you can see, a location which
is a nested document, even coordinates here,
we got a time zone, got an e-mail, login data, date of birth which is both the date and the current age, some
registration date,
let's say this is for a web platform where people can manage their contacts and each contact therefore also
has a date when the contact signed up and so on,
so we get this basic dummy person data which we can use.
So now let's run a query and let's find all people who are older than 60,
so for this, I will clear my shell here and I will then reach out to contacts, run a find method here
and I'm looking for the dob.age field,
so for this field in the embedded document and I'll have a greater than query and the greater than query
will be looking for people older than 60. Let's also pretty print this and we get a bunch of results, 61
is the age here, 61 again,
so there are some people who are older than 60 as it seems.
You can also have a look at how many results we got by adding count at the end,
so 1222.
Now of course this was a super fast query but we also don't have that many documents in this collection,
that's important to keep in mind,
now in order to determine whether an index can help us or to see what mongodb actually does,
mongodb gives us a nice tool that we can use to analyze how it executed the query
and this tool is a simple method we add to our query. Here after you reach out to the collection,
you can add the explain method and then chain your normal query,
explain works for find, update, delete
not for insert,
so it basically works for the methods where you well narrow down documents, where you find documents
and then here we can of course repeat our condition, looking for dob.age to be greater than 60.
Now here we get the detailed description of what mongodb did and how it derived our results,
mongodb thinks in so-called plans and plans are simply alternatives it considers for executing that query
and in the end it will find a winning plan
and I'll come back to how mongodb determines this later
and that winning plan is essentially what it did to get our results
and you see here, the winning plan was to do a full collection scan.
We could also have rejected plans but for this, we would need alternatives and without indexes, a full
scan is always the only thing mongodb can do,
so there were no alternatives and therefore the only approach we had of course is the winning plan.
Now we can get even more detailed output by re-running that command
but now we can pass an argument to explain and that argument is a string where we control the verbosity
of this command.
If you pass execution stats here, make sure you get that typed correctly with a capital S and a lower
case e,
you find a detailed output for this query and how the results were returned.
There you'll see that the overall query took 5 milliseconds which is of course super fast but our collections
is also not very big
and if it were bigger, if it had millions of documents, this number would of course scale up and you see
that we had to look at 5000 documents in order to return our 1222,
so there's quite a big gap here and this already is a sign that this is a kind of an inefficient query.
Now let's add an index and see how this changes things,
we do add an index to a collection by typing db contacts and then create index.
Now an index is defined as a document here
and the first value,
the key here is the name of the field you want to create an index on
and in my case that is dob.age,
.
So you see you can create indexes on embedded fields just as you could use a normal field,
so you can use top level fields, you can use embedded fields, doesn't matter. Then the value is whether
mongodb should create that list of values in that age field in an ascending or descending order,
so it can sort by assigning or descending order.
If you add a one here, it'll be ascending order,
so lower values come first,
higher values towards the end,
if you add a -1 here, it's descending. What you choose here in the end doesn't matter too much
even if you do sort your results and you sort to the opposite direction, it will still be sped up because
mongodb can traverse that index in both directions,
so you can actually choose what you want here and I'll go for ascending.
Now this created an index, you see we had one before,
we'll see which index that was in a second,
now we have two.
So with that, let's repeat our explain command here
for people older than 60 and if we do that, we should see that now now the execution time is down significantly
from 5 to 3,
so obviously we're talking about small numbers here
but the main thing is it was sped up.
We also see that there are two execution stages now
and we also see that the first stage, the input stage was an index scan, in the other output, that would
be our winning plan essentially.
So it did not do a full collection scan but instead an index scan
and there you see that it returned 1222 documents or not documents to be precise
but keys in the index with their respective pointers at documents,
so the index scan does not return the documents but just the keys in the index and the pointers to the
documents.
It's the next stage,
the fetch stage which will then take these pointers returned from the index and reach out to the actual
collection and then fetch the real documents from there
and therefore in the end, we see here we had to only look at 1222 keys in our index to reach 1222
documents which are returned.
We also had to look at these documents because the index only has the pointer set to documents,
so the index just narrows down the set,
we still have to go through the collection and get the documents from there to return them in the end
but this sped up our query and this is how an index can help us.
Now before we dive deeper into different types of indexes, let me also show you something interesting
about this dataset which helps you understand indexes a bit better.