Using a compound index as shard key

MongoDB University M103: Basic Cluster Administration
#1

Hi everyone,

I have a question with regards to using a compound index as a shard key:

I understand that you can specify each prefix up to the entire shard key & still get a targeted query like so:

  • If I have a shard key like so: {"sku": 1, "type": 1, "name: 1"}

  • The following will be targeted queries:

               db.products.find({ "sku": ....})
        

           db.products.find({ "sku": ...., "type": ...})
       

          db.products.find({ "sku": ...., "type": ..., "name": ...})

  • …and the following are not targeted queries:

        db.products.find({ "type": ....})

    db.products.find({ "name": ....})

This is all quite simple to understand, but what if I have a query such as the one below:

db.products.find({ "name": ...., "type": ...., "sku": ....})

Does the prefix/order matter in this case?

Thanks in advance to anyone who can help!

1 Like
#2

Don’t hold me to this, :slight_smile: . From the Performance course, the order of your fields in queries do matter depending on your index. It has something to do with predicate. I’ll have to brush up on it.

Shard Keys

You cannot specify a multikey index as the shard key index.

However, if the shard key index is a prefix of a compound index, the compound index is allowed to become a compound multikey index if one of the other keys (i.e. keys that are not part of the shard key) indexes an array. Compound multikey indexes can have an impact on performance.

https://docs.mongodb.com/manual/core/index-multikey/

2 Likes
#3

In SQL database world the sequence of the compound index is key. I don’t imagine it is any different with Mongo

db.products.find({ “name”: …, “type”: …, “sku”: …})

Instead of moving through the data quickly, the above would have to jump around.

So the find would look for a index on name.

Unfortunately Mongo doesn’t support multiple index finds. That is using 3 different indexes simultanously, so an index on name, an index on sku and a index on type.

1 Like
#4

Hi @trungEdm,

Thank you for taking the time to respond to my question and thank you for the link ( I needed to brush up on how compound multikey indexes work). In this particular case (I’m trying to compare this example to final question #6), however, I am assuming that none of the indexed fields is an array.

I am currently reading about prefixes via the MongoDB docs (https://docs.mongodb.com/manual/core/index-compound/index.html) and you are correct that order matters in a compound index.

Thanks again:-)

#5

Hi @NMullins,

Thank you for taking the time to respond to me! I think that I am getting the concept now. For some reason, I had it in my mind that the sequence could be flipped so long as all of the fields in the prefix were/are included.:grinning:

Cheers:-)

1 Like
#6

The sequences can be flipped sometimes. It’s a bit tricky. :slight_smile: It’s covered in the Performance course, which I’m going over. There’s a very similar question as #6.

Hint for #6: Is it more important to find the _id or the country? Whatever is more important should be in the query.

1 Like
#7

order doesn’t matter mongo will automatically identify prefix and choose index.

1 Like
#8

I know, it CAN be tricky! :thinking: I did take the Performance class a while back (it’s been over a year… I have just pulled up my notes from that course and I am going to give them a look through again to brush up.

Thanks for the hint! :joy:

Cheers:-)

1 Like
#9

Thank you @naseer561! I do recall reading this in the docs somewhere. I may have been confusing prefixes (in compound indexes) and how they relate to sorts.

Cheers:-):smile: