Hello
Have a question on approach to take for best way to query the data which is in following format
{
Customer ID : 12345
Product ID : ABC
Account: 878
}
We have document as modeled above in CB with a distinct customer count of around 2 million.
Distinct product count that we have i.e. Product ID mentioned above in the document is around 3K
Requirement that we have is to get list of all customers who have a certain product ID,
For this requirement is it advisable to create an index on Product ID attribute? Reason that i ask this question is as mentioned above it is only 3K disctinct products that we have which are taken by these 2 million customers.
What is an optimal way to approach this requirement
Regards
Srinivas
Hi @srinivas,
I think I will need more information.
For example, how many documents will be present in the database with a specific (product id, customer id) combination?
Case 1: If there is “always only one” document for a specific (product id, customer id) combination, then you can create an index on “product id” and “customer id”. Note that both of these fields should be present in the index to get the maximum performance. Please see covering index.
Also, if there are other types of documents in the database, then I believe that you have a “type” field in the documents. So, you can use where clause while creating the index.
Case 2: If the database can have multiple documents for same (product id, customer id) combination, then any index you create with this document format will have some extra overhead. This extra overhead can be reduced by introducing a new type of document in the database which maintains unique mapping between product id and customer id. And then you can create the index only for this new type of documents (using where clause).
Please let me know which of the above cases is relevant to your application, and we can work out a better solution.
Hope this helps.
Thanks.