Using a compound index as shard key

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

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

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

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:-)

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

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

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

2 Likes

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

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:

You said the order of the fields matters, however when I look at the result of the quiz
Quiz the question was
Given a collection that is sharded on the following shard key:

{ "sku" : 1, "name" : 1 }

Which of the following queries results in a targeted query?

and the two queries below were part of the answer
db.products.find( { "sku" : 1337, "name" : "MongoHacker" } ),
db.products.find( { "name" : "MongoHacker", "sku" : 1337 } )

That tells me that db.products.find( { "sku" : 1337, "name" : "MongoHacker" } ) is the same as db.products.find( { "name" : "MongoHacker", "sku" : 1337 } ) meaning the order doesn’t matter here.
Am I wrong?

Hi @Alpha_Ly,

The above two queries are actually identical and can be targeted using the shard key.

But, the order of fields in find() does not matter unless you use .sort().

For more information take a look here.

Hope this helps. Let us know if you have any questions.

Kind Regards,
Kushagra

Both are true.

In find(), the order does not matter since a equals b and c equals d will provide the same result set as c equals d and a equals b.

However, while creating index the order does matter. For example, with the index { "sku" : 1, "name" : 1 }, find({sku:"..."}) should result into an index scan while find({name:"..."}) should result into a much slower collection scan. But, if your index is { "name" : 1, "sku" : 1 } the index vs collection scans would be the other way around.

1 Like