Now we had our first look at indexes,
now let's again look at a single document in our contacts database and we created an index for the age
here,
now we dropped that in the last lecture but previously we did.
Now of course you can not just create indexes on fields that have a number,
you can also create indexes on fields that have some text and that makes a lot of sense because well,
text is also something you regularly search for and it can perfectly be sorted. Booleans on the other hand are something
that don't make too much sense because since you only got two kinds of values,
true or false,
chances are that the index will not speed up your queries too much.
Now let's create a second index or actually a first one because I dropped one
and let's now use a text index and for that, I'll try creating an index on the gender, male here.
I'll do this on my collection with create index and I'll look for the gender field to be sorted ascendingly.
Now of course one problem you might already think of is that for gender,
just like for a boolean, we typically only have two values and indeed
if I do run explain command with execution stats on my contacts and I try to find all contacts where
gender is equal to male, I should get back a lot of keys right,
I should get back like half the documents because this random collection of persons is pretty evenly
distributed
and indeed of my 5000 documents, 2400 are male.
So this again is probably an example for an index that doesn't make too much sense or that doesn't really
speed up our queries because we fetched a majority or a large chunk of our collection every time,
still we use the index here of course.
This also tells us something about the winning plans and rejected plans by the way, you see that mongodb
didn't seem to consider a collection scan because we have never checked the plans,
so for the rejected plans, it also looks at other indexes but not really at the collection scan as an alternative.
So this index alone doesn't make too much sense but there is another kind of index we can use because
let's say we want to find all persons who are older than 30 and male or older than 40 and male
and for this, we can create a so-called compound index.
Now let me drop this index
first of all, db contacts drop index and I'll drop my gender ascending index here and let's create
a new one here
and this index will not just have the gender but actually as a first key, let's say I want to look for
dob.age and that sorts this in ascending order too.
Now the order of these two fields here does matter because a compound index simply is an index with
more than one field, like here
we got two fields and this will essentially store one index where each entry in the index is now not on
a single value but two combined values. So it does not create two indexes, that's important,
it creates one index but one index where every element is a well, connected value. So now it will simply
create pairs of ages and genders, so we'll have 30 male, 30 male, 30 female, 31 male, 31 female and so on
in the index list and the order here defines which kind of pairs mongodb creates,
does it create let's say 31 male field or a male 31 or 33 field and this will be important for your
queries as you will see.
So let's create that index, created the index successfully and now we can utilize such a compound
index with two different queries.
The obvious query that takes advantage of the index is that
if I explain without execution steps here, I find
query where
I try to find all people where, whoops, the age is let's say 35 and all the people are males,
so I can also set gender to male.
So I got two elements in my filter here,
the age and the gender and these are the two elements I also have in my index, here
the order does not matter
by the way. If I hit enter, you see it actually did an index scan with our index name and this name is
auto-generated,
it indicates that this is a compound index because the name is generated from the values that are placed
in the index.
So this is a compound index and this speeds up our queries for requests that are well, going to these
two fields because we have these two in an index. Another query that is sped up by that same index
is if I just look for the age,
if I do that, you'll see here
it also used an index scan and it used that same compound index even though I never specified the gender
in this request here.
But the compound index can be used from left to right so to say,
so if you have age and gender in there and the leftmost value as you created the index was age and then
the second value was gender, then you can use this index either for all finds that look just for the age,
so for the left part or left and gender. Gender alone won't work though,
so if I try to look for all males with gender male, you'll see that of course works but it uses a collection
scan and not an index scan because it can't look into that second value standalone
and that makes a lot of sense because if you got a couple of pairs of values in that index, 33 male,
33 male, 33 female and so on, then you can obviously use that index if you just look for 33
because you just have to go through all the values because they are sorted by these key value pairs and the
first value of course defines the sorting for the overall index,
so 33, 33 comes before 34. The male and female parts are then also sorted but only within
their category,
so only for 33, 34, 35,
of course the male and female parts are not sorted on the overall list,
makes sense right, because they're grouped together with the age numbers.
So therefore, you can utilize a compound index from left to right,
so if you had a compound index with three or four elements, you can have up to 31, then you could also
just use the first two,
the first three or all four or just the first
but you can't use the fourth one or the third one.
These are some restrictions you have on compound indexes but compound indexes in general allow you
to speed up queries that use multiple values
if you create a compound index on these multiple values.