Confusing explanation on Multi Key Index as a shard key in the documentation

[Nikhil Naib]

Hello,
              The documentation link given below holds an explanation on the use of compound multi-key index as a shard key, which I am not quite clear with.

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

Shard Keys

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.

Could you please elaborate and simplify the above point mentioned in the documentation? Some example would also be helpful.


Thanks,
Nikhil

[Wan Bachtiar]

Could you please elaborate and simplify the above point mentioned in the documentation? Some example would also be helpful.

Hi Nikhil,

First, let’s start with compound indexes, a single index structure holds references to multiple fields. For example, you may create a compound index consisting of three fields a, b and c:

{"a": 1, "b": 1, "c": 1}

A shard key index could either be an index or a prefix of a compound index. Using the example above, you could use :

{"a": 1}
{"a": 1, "b": 1}
{"a": 1, "b": 1, "c": 1}

Where the first two being prefixes of the compound index, and the third being the compound index itself.

Going back to the manual on shard keys being a multi-key index. A shard key index cannot be a multi-key index. However you can use the prefix of a compound index as a shard key, as long as not the multi-key part. Using the above example again, if field c contains array values, then only the following prefixes now can be use as a shard key:

{"a": 1}
{"a": 1, "b": 1}

Regards,
Wan.

​

[Nikhil Naib]

Hi Wan,
             Thank you so much for the great explanation with example! Now I completely got the point. Just one thought though; even allowing the multi key index as part of the shard key would make the shard key far less efficient as there is one entry in the per element of array due to multi key index. e.g. customer_id and order_item_id. IMHO restricting the user (as part of best practices) from creating such a key would be a better option. 

Best Regards,

Nikhil

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to a topic in the Google Groups "mongodb-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mongodb-user/ZcYTfDgkn7U/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mongodb-user+unsubscribe@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/a79afee3-53c4-4ec8-b1a1-d8933dbbfeba%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Wan Bachtiar]

even allowing the multi key index as part of the shard key would make the shard key far less efficient as there is one entry in the per element of array due to multi key index

Hi Nikhil,

Please note that you cannot specify a multikey index as the shard key index.
As mentioned, you can only use the prefix(es) of a compound index as a shard key, as long as it’s not the multi-key part. i.e. you can’t use the array field.

See also Shard Key Limitations.

Regards,
Wan.

​