Final Exam Question 7 Index

Hi everyone,

Why would the index { in_stock: 1, price: 1, name: 1 } create two index keys?

  • { in_stock: true, price: 2.99, name: ‘Soap’ }
  • { in_stock: false, price: 7.99, name: ‘Knife’}

As boolean is neither array nor sub-document…

Hey @Joey_Du,

These are the two sample documents given to you and not indexes.

Although they have very low cardinality, compound indexes can be created on boolean too.

The question has given you these two indexes:

1. { categories: 1, price: 1 }
2. { in_stock: 1, price: 1, name: 1 }

These are the documents on which the queries will run ie. your collection contains these 2 documents:

1. { price: 2.99, name: "Soap", in_stock: true, categories: ['Beauty', 'Personal Care'] }
2. { price: 7.99, name: "Knife", in_stock: false, categories: ['Outdoors'] }

Now the question is asking you for the given 3 queries, what options will be true.

Since this is an exam question, I can’t pinpoint to you the exact answer but kindly note that the index options given to you are Compound Indexes and you may want to go through the lectures again on them as well as read more about them and prefixes since I feel you are getting confused here.

I would also recommend you try creating a test collection in compass and inserting these two documents in there and testing out the indexes against the queries given. Trying queries out is always the best way to learn and understand. :dizzy:

Please let us know if there’s still any confusion in this or if I missed out on anything. Feel free to reach out for anything else as well.

Regards,
Satyam

you can try and see yourself that there is only 1 index created by this indexing.

db.indextest.insert({ price: 2.99, name: "Soap", in_stock: true, categories: ['Beauty', 'Personal Care'] })
db.indextest.insert({ price: 7.99, name: "Knife", in_stock: false, categories: ['Outdoors'] })
db.indextest.createIndex({ in_stock: 1, price: 1, name: 1 })
db.indextest.getIndexes()
[ ...
       {
                "v" : 2,
                "key" : {
                        "in_stock" : 1,
                        "price" : 1,
                        "name" : 1
                },
                "name" : "in_stock_1_price_1_name_1",
                "ns" : "test.indextest"
        }
]

So, can you try to explain what is it you meant for “create two index keys”?

by the way, the question is about how indexes are used by the queries. you can create the other index too and try to see how many are created. then use “explain” to see which indexes are used in which query and in which stage.

you don’t have to memorize everything. just open up a console and write queries there then examine the results.

Hey guys @Yilmaz_Durmaz @Satyam

Thanks for your kind replies! I opened a test collection, inserted two documents and ran two createIndex commands as suggested. This is what I got:

MongoDB Enterprise atlas-3ce2jl-shard-0:PRIMARY> db.indextest.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_"
        },
        {
                "v" : 2,
                "key" : {
                        "categories" : 1,
                        "price" : 1
                },
                "name" : "categories_1_price_1"
        },
        {
                "v" : 2,
                "key" : {
                        "in_stock" : 1,
                        "price" : 1,
                        "name" : 1
                },
                "name" : "in_stock_1_price_1_name_1"
        }
]

I think I got a bit confused on the word “index keys” here. So this is 3 indexes (one original _id, two created by me), and 5 index keys (as the three-element-array categories in the “categories_1_price_1” index giving it 3 index keys and the other 2 plain ones each giving it 1 index key)? Is that correct?

Thanks again!

Ah, No! You missed the point a bit far :stuck_out_tongue_winking_eye:

index name and index keys are directly related but are independent of other indexes.

when you say “index abcxyz with categories and price” it is like you make a new ordered collection of documents, but they will hold only 1 thing in them: where will I find documents from that “abcxyz” collection corresponding to this “categories” key, and among these which are corresponding to this “price” key. indexing is making direct addresses to access those documents faster without any search because indexing has already made the search beforehand.

“find me documents with these categories” will directly go look at indexed collections starting with a “categories” key (and in asc/desc order), and return only them.

id” index holds addresses of documents with, you see, their physical addresses on memory/disk, “categories_1_price_1” holds again addresses but first ordered by categories and then price, and “in_stock_1_price_1_name_1” does the same, hold the physical address, with the same order the names appear.

if your query does not match these keys in “$match” and “$project” or the order of them in “$project” and “$sort”, server has to look at “all” documents because it does not have a pre-made collection (meaning an index) that suits the query.

What about " To index a field that holds an array value, MongoDB creates an index key for each element in the array. These multikey indexes support efficient queries against array fields." from https://www.mongodb.com/docs/manual/core/index-compound/ ?

This sounds like: an index key for each element, “categories” value is an array of 3 distinct values, so 3 index keys there.

And the detailed explanation of this question states:

there would be 5 total index keys:

* { categories: 'Beauty', price: 2.99 }
* { categories: 'Personal Care', price: 2.99 }
* { categories: 'Outdoors', price: 7.99 }
* { in_stock: true, price: 2.99, name: 'Soap' }
* { in_stock: false, price: 7.99, name: 'Knife'}

The additional index keys are due to the multikey index on categories.

What the 5 total index keys exactly are here?? :smiling_face_with_tear:

I finally got the confussion you are having :wink:

when “indexing”, “indexes” and “index keys” are two different things in the sense of what they hold in them.

following my above post, again analogical, “indexes” are the names of those new collections, here we have 2 indexes so two collections. and “index keys” are the documents in them each having an _id with the value of “unique” values present in main document and a “docs” field array containing the addresses of documents.

say, we have these 3 documents:

{_id:7, istrue:"yes", isopen:"yes"}
{_id:13, istrue:"yes", isopen:"yes"}
{_id:42, istrue:"no", isopen:"no"}

if you make an index on “istrue” field, then the index itself will hold values like this (not the actual representation, or is it?):

{_id:"yes", docs:[7,13]}
{_id:"no", docs:[42]}

the number of “index keys” will be the number of “unique” values of that field, so “true” and “false” values in “in_stock” will make 2 keys.

when you query the main collection with db.test.find({istrue:"yes"}), returned documents will be those whose “_id” fields are contained in the “docs” array of the document having id of “yes”

I hope this clears your mind :slight_smile:

1 Like

What about “price” and “name”? They appear in the index, but having non-unique values makes them unqualified for an index key? But how can we be sure “name” is not unique…

Or, one index can only have one field determine the number of index keys? Like the way you can only have one array field when creating an index…

for the exam question, you are given to have “only” 2 documents. no more, no less. thus only 2 keys. but you cannot actually say it was due to different “in_stock” values, or different “price” values, or different “name” values. just for starters, they will be created in the order you give the fields into “createindex”.

you cannot determine “uniqeness” before inserting a document into collection. but once you do, you will know if “name” is unique or not.

if you have 100 documents, you can initially assume 100 unique names with 100 unique keys. but if there are repeating names, then the number will go below 100, but will never be above 100 because you have only 100 documents. at best, all names will be the same hence you will have only 1 key.

for compound keys, your key will be the combination of those fields. worst case will be MxN where M is the number of values of first field, and N is for second field. for a “size-color” collection you will have for example “small-red”, “medium-red”, “small-blue” and “medium-blue” if you use “small/medium” and “red/blue” 2x2=4 keys. but this requires 4 different documents to do so.

2 Likes

Thanks very much!! Finally wrapped up my mind around indexes and index keys :smiling_face_with_tear:

1 Like