Now we had a look at single field and compound indexes and what we can do with them and how we can
measure the efficiency,
all super important things.
Now let me introduce two new types of indexes and the first one is the so-called multikey index.
For this, I made sure that I dropped my contacts collection, so that huge collection which we imported
before, I already did drop it,
hence I see false here
and now I will add a new value here, contacts insertOne and that new value,
am I
and here I will add hobbies which happens to be an array, an array of strings,
let's say cooking, sports.
I'll also add another array, addresses, let's say I have multiple ones,
this will be an array and each array is a document there,
so embedded documents in an array. There to keep it short and simple,
I'll just have the street, Main Street, add another document with the street, let's say second street,
doesn't really matter.
If I hit enter here,
let's quickly have a look at what I inserted,
this is looking good,
I got Max, two hobbies which are just strings and two addresses which are documents. Now
let's index an array because that is possible in
mongodb too.
For this I'll of course create an index again as we did it before and this time, I will index hobbies
and if you remember, hobbies was an array right,
you see it here, it was an array of strings.
Let's hit enter and this works,
now let's try using that
and first of all, let's execute a query where I look for hobbies being equal to sports and I do find
something, I can pretty print that to make it easier to read,
I do find Max of course because I do have sports in there. Now
the interesting thing is if I explain this with execution stats, I see that the winning plan was an
index scan and we see that there, isMultiKey is set to true for my hobbies index. Mongodb treats
this as a multikey index because it is an index on an array of values and technically, multikey indexes
are working like normal indexes but they are stored up differently.
What mongodb does is it pulls out all the values in your index key,
so in hobbies in my case here, so it pulls out all the values in the array I stored in there and stores
them as separate elements in an index.
This of course means that multikey indexes for a lot of documents are bigger than single field indexes
because if every document has an array with let's say four values on average and you have a thousand
documents and that array field is what you index, you would store four thousand elements because four
times one thousand.
So this is something to keep in mind,
multikey indexes are possible but typically are also bigger,
doesn't mean you shouldn't use them.
If you typically query for an array and the value in an array,
well then it makes sense to turn this array into a multikey index, that is perfectly fine.
So now we learned that we can use a multikey index and we are using one on hobbies,
now let's also add one on addresses, contacts, create index and there, I'll index addresses,
one. Now this also worked,
it created that index
and now let's try to utilize that.
Let's reach out to contacts and let's explain what's happening with execution stats, so that we can see
if that index gets used
and let's find all addresses where the street is Main Street. If I hit enter here, we see it actually used
a collection scan and not our index.
The reason for that is that our index of course holds the whole documents and not the fields of the
documents,
so mongodb does not go so far to pull out the elements of an array and then pull out all field
values of a nested document
that array might hold.
So our index would only get used if I were not looking for the street in the addresses
I have in that array but if I were looking for addresses holding some document,
so if I were looking for addresses where street is Main Street, if I were looking for that, you see it
would have used an index scan because it is the whole document which is in our index in the end. Mongodb
pulls out the elements of the array for addresses as single element in the array happens to be
a document,
so that document is what mongodb pulled out and what mongodb then stored in the index
registry and therefore this is something to be aware. What you can do though is you can create an index
on addresses.street,
this also will be a multikey index as you can see if I now repeat my earlier query where I was looking
for addresses street,
if I repeat that query now,
now you see it uses an index scan for this and it is a multikey index,
so you can also use an index on a field in an embedded document which is part of an array
with that multikey feature. You should just be aware that of course using multi multikey features
on a single collection will quickly lead to some performance issues with rights
because for every new document you add, all these multikey indexes have to be updated
and if you add a new document with 10 values in that array which you happen to store in a multikey
index, then these 10 new entries need to be added to the index registry
and if you then have four or five of these multikey indexes per document, well then you quickly are
in a low performance world.
Still multikey index is super helpful if you have queries that regularly target array values or even
nested values or values in an embedded document in arrays.
There are a couple of restrictions or one important restriction to be precise when using multikey indexes
and that is that if you add an index, a multikey index and you add it as part of a compound index, that
is generally possible,
I can add name in ascending order and name is not an array, it's just a single field and I can then add
hobbies like this,
this works. However what won't work is that I say I want have a compound index made up of two or more
multikey indexes,
so addresses one and hobbies one will not work because you can't index parallel arrays.
The reason for that is simple,
mongodb would have to store the cartesian product of the values of both indexes, of both arrays,
so it would have to pull out all the addresses and for every address, it would have to store all the
hobbies.
So if you have two addresses and five hobbies, you already have to store 10 values and that of course
becomes even worse the more values you have in addresses,
so that is why this is not possible.
Compound indexes with multikey indexes are possible as you see here but only with one multikey index,
so with one array, not with multiple arrays. You can have multiple multikey indexes as you can see here
in separate indexes
but in one and the same index, only one array can be included.