Is index order is important?

Couchbase Server
1

Hi everyone,

I am wondering if I change the index order, mechanism of index is changed or not ?

For example ;

create index geofenced on test(_class, appId, geofenceBased, status) WHERE deleted = FALSE

or

create index geofenced on test(_class, appId, status, geofenceBased) WHERE deleted = FALSE

Same as queries.

Does it matter if I write geofenceBase first;

where deleted = FALSE AND
_class = “com.myexample.test.push.data.entity.PushEntity” AND
appId = “whitelabel” AND geofencedBase = true AND status = “draft”

or last ?

where deleted = FALSE AND
_class = “com.myexample.test.push.data.entity.PushEntity” AND
appId = “whitelabel” AND status = “draft” AND geofencedBase = true

Any idea please? Which one should be used for which situations ?

Thank you

2

Check out Rule #11 https://blog.couchbase.com/create-right-index-get-right-performance/

4
  1. First priority is the keys with equality predicates. In this query, it’s on state and type. When there are multiple predicates of the same type, choose any combination.

So I am using equality predicates in my case, then it does not matter for this situation, am I right?

5

As far as query is concerned no difference. Index uses b-tree type of structure. If you want to minimize disk i/o you can use high cardinality field first.

1 Like