How to account for values in order in the index

SQL++
, , ,
1

Hey Folks,

Have a question regarding order of the index in the query.

lets say i have an index that covers the following fields, in this exact order in the index.
a, b, c.

I want to query on a and for this particular query c as well. I know in n1ql by adding the b in between would improve the result of this query, because it follows the form of the index. and I can simply suggest AND b IS NOT MISSING.

My problem is there is a chance b can be missing. Is it possible for me to include b in the query but not care whether it is there or not? outside of doing AND ((b IS NOT MISSING ) OR (b IS MISSING))

Any ideas?

2

Don’t include b in predicate

In 6.5 it automatically optimized Example 4 https://docs.couchbase.com/server/current/learn/services-and-indexes/indexes/index_pushdowns.html

1 Like
3

I am aware of 6.5, I should have mentioned we are using 6.0.4, is this feature new to 6.5? is their a way to work around in 6.0.4?

4 
CREATE INDEX ix1 ON default(a,b,c);
SELECT * FROM default
WHERE a > 10 AND c > "20"
ORDDER BY a,b,c;

Above query works in 6.0.4 and uses index order.
In 6.0.4 It only pushes a to indexer and c predicate applied post index scan. Check EXPLAIN
In 6.5.0 It pushes a, c to indexer

1 Like