The collection:
A record from my collection look like this:
{"visitor-hash": x, "website-name": y, "visitor-location": z, "visitor-details": a}
Now in my collection, we'll get a lot of entries for same , i.e if the website is getting 1M visitors, then we'll have 1M entries in this collection.
PS: there are 80+ attributes like visitor-location which can never be indexed.
The queries that I need to fire quite often:
Give me all records for website-name = y group by visitor-location Give me all records for website-name = y where visitor-details = a Give me distinct values for visitor-location where website-name = y
Sharding requirements:
Now the correct shard key is visitor-hash as it will balance the load on both the shards. But, since I need to run a lot of queries which does not have shard id in it (i.e visitor-hash) then all these queries will become a scatter-gather type query which will be slow and I can not fire them often then.
The next choice for my shard key becomes website-name as it will always be present in all the queries and even the group by will work with less pain. However, this will make the shards unbalanced as what if website-name y is the top most visited website.
So, my questions here are:
- Is a join fired across shards really slow? Is it even a recommended thing.
- Are scatter gather queries really slow?
- [unrelated] Can we index all the attributes (upto 64, as that's the max that we can index) for gaining speed?
Technical details:
- Storage engine: WiredTiger
- As of now, Indexes are not present on any of the visitor-location like 80+ attributes. However, we are plaing to create some indexes on most used attributes.