Composite Predicate Pushdown


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.

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. Rule #11