I currently have an index on 4 different properties:
CREATE INDEX test ON default(docType, brandId, missionId, status) WHERE docType="Submission".
In my particular use case, sometimes the filter might occur (excluding the docType) in the 1st, 2nd, 3rd property, other times in the 2nd and 3rd and, occasionally, just on the 3rd. Is there a way I can handle every scenario with a single index, or would a better approach be to create 3 different indexes for each use case?
My current installation is Couchbase 4.6.1. Thank you
I thought about this approach, but I was under the impression that when building an index we should try to use more restrictive parameters first.
So in the context of a brand, it would not make sense to have the index in status first because it would contain all submissions of all brands in a first step, hence the brandId being more restrictive, but perhaps I am not thinking correctly.
keeping docType as part of index or not depends. Queries needs have predicate on leading key. If you have all queries have predicate on docType and some don’t have other fields then it may better keep in the index.
That query will still work, this index is just smaller. Because you already have docType in the WHERE you don’t need to include it in the index keys.
CREATE INDEX test ON default(docType, brandId, missionId, status) WHERE docType=“Submission”;
CREATE INDEX test ON default(brandId, missionId, status) WHERE docType=“Submission”;
I’m not sure what you mean about the template injecting the docType, I assume that if you have different docTypes you have different indexes for them as well? Since the index here won’t help a query with a WHERE docType = “AnythingElse”