thanks @frasdav for raising this. Will add details to CREATE INDEX & related documentation.
When defining compound indexes, the order of index keys is very important, both for selecting the index for a query, and to leverage the index order (to avoid sorting in the N1QL query engine).
Infact, prefix keys are required to use the index. For ex: In following index,
CREATE INDEX idx1 on default (idx_key1, idx_key2, idx_key3, …)
- {idx_key1} is prefix-key for idx_key2,
- {idx_key1, idx_key2} are prefix-keys for idx_key3, and so on…
To leverage the index, a SELECT query must have reference to idx_key1, which is a minimum prefix. It is better to have as many prefix keys as possible. In fact, for better performance, the keys with low-selectivity should be placed before the keys with higher selectivities.
Following blog explains index-key selectivities and composite indexes:
Understanding index spans:
https://developer.couchbase.com/documentation/server/4.6/performance/index-scans.html
hth,
-Prasad