There will be about 3 billion docs in the collection across shards. Assuming I would be using queries that is not fulfilled entirely by the index(es). And I am not using the keys for sorting.
Does it make sense to have indexes as follows (which match the queries):
{"sid":1, "cid":1, "mid":1}
{"cid":1, "hid":1, "mid":1}
{"mid":1, "hid":1, "sid":1}
{"hid":1, "sid":1, "cid":1, "mid":1}
{"mid":1, "cid":1}
{"mid":1, "sid":1}
Or:
{sid:1}
{cid:1}
{hid:1}
{mid:1}
This is a valid question. You are not using these keys for sorting so I will assume your queries contain no sort, since you must be aware (to have said what you did) that index inter-sectioning cannot work across sorts.
I would say: better to have the compound indexes.
Even though it is more indexes to maintain it makes for straight answers to your queries without the problematic calculations required for inter-sectioning. I mean you are doing this over 3.5b records, that won't end well.
The compound index approach puts the weight of maintenance on the insertion/updating side which is much better than on the query side. Even if the indexes only partially fulfill the queries at any given point in time.
It seems that MongoDB's intersectioning has got more advanced:
In general, each index intersection involves two indexes; however, MongoDB can employ multiple/nested index intersections to resolve a query.
http://docs.mongodb.org/manual/core/index-intersection/
So even though it can use more than two indexes the usage of 3 intersections would still cause a nested intersection to occur which doesn't sound pleasant.
I did answer a similar question some time ago whereby it was said by 10gen that inter-sectioning is essentially a last resort, not something that is desirable.
