https://docs.couchbase.com/server/6.0/learn/services-and-indexes/indexes/index_pushdowns.html
Why for (age = 20) AND (name BETWEEN "joe" and "mark") where clause
CREATE INDEX `idx_age_name` ON users(age, name);
is better than
CREATE INDEX `idx_age_name` ON users(name, age);
In my RDBMS background and large data set experience , second index is better
Couchbase index are based on b-tree scans are range scans.
If first key is range and second key is equality still it needs to read the index page and avoid them.
Based on start and stop keys.
Example:
name BETWEEN “joe” and “mark” Qualifies 100,001 items
age < 20 qualifies 200,000 items
age = 20 qualifies 1 item
age > 20 qualifies 800,000 items
Index needs all the pages based on composite index and apply individual parts of index keys. There can be lot of disk I/0.
https://blog.couchbase.com/create-right-index-get-right-performance/ Rule #11