Composite index order

SQL++
,
1

I have select query that java sdk generates using N1QL utils. Below is the query.

select * from users where user.status = “ACTIVE” and Date BETWEEN “2019-06-26” AND “2019-06-30” and group in [“xyz”, “abc”] and service = “XXX”;

This query can be changed dynamically , some times only two predicates can be present and some times 6 predicates with AND operator.

Question : Do I need to use the predicates in specific order (i.e some times user.status can be last predicate instead of first predicate). Does the order matters ?

This query can change in future with 6 to 8 predicates. So how many indexes need to create to get best performance.

2

Create one index for each field that might be used in the query. The engine will user the indexes corresponding to the fields that appear in the query, and do something called an IntersectScan to compute the result.

Your indexes should be of this form:

create index service_idx on users(service)
3

Thank you for your reply.
I have one mandatory field in my predicate . At any time one of the fields can be in present in the query. Let say i have 6 fields then i have 720 combinations). So creating composite key with mandatory key and have all the other fields would be sufficient or need to create individual indexes ?

4

creating composite key with mandatory key and have all the other fields would be sufficient .

When index keys are continuous in predicate then those only pushed to indexer others applied post index scan. If you want you can add predicate field1 IS NOT MISSING. The predicate order is no impact on the index selection.

The following link has all the info https://blog.couchbase.com/create-right-index-get-right-performance/

https://blog.couchbase.com/n1ql-practical-guide-second-edition/