Creating correct index

SQL++
1

Hi everyone, I have a question and kind a stuck with it. I want to use 3 query type as below.

1-)

SELECT META(appconnect).id AS _ID, META(appconnect).cas AS _CAS,appconnect.* FROMappconnectWHERE (appId= "whitelabel" AND geofenceBased = false ANDdeleted= FALSE) AND_class= "com.commencis.appconnect.push.data.entity.PushEntity" ORDER BYcreatedAtDESC LIMIT 25 OFFSET 0

2-)

SELECT META(app).id AS _ID, META(app).cas AS _CAS,app.* FROMappWHERE (appId= "whitelabel" AND geofenceBased = false and status = "DRAFT" ANDdeleted= FALSE) AND_class= "com.test.push.data.entity.PushEntity" ORDER BYcreatedAtDESC LIMIT 25 OFFSET 0

3-)

SELECT META(app).id AS _ID, META(app).cas AS _CAS,app.* FROMappWHERE (appId= "whitelabel" AND status = "DRAFT" ANDdeleted= FALSE) AND_class= "com.test.push.data.entity.PushEntity" ORDER BYcreatedAtDESC LIMIT 25 OFFSET 0

In summary, geofenceBased and status is changing - All together, only geofenceBased, only status.

Can I handle all quries in one index and my index is:

create index myIndex on app(appId, geofenceBased, status, createdAt DESC)
WHERE deleted = FALSE AND _class = “com.push.data.entity.PushEntity”;

If I use like this; If I query with only geofenceBased or and status it uses OrderedIntersectScan, but if I query geofence and status together it uses my index.

How can I create index for my problem but the using minimum index ? If you don’t mind, I want to ask what is OrderedIntersectScan. I am new at couchbase. Any idea please

2

Create 3 different indexes provide USE INDEX hint for better performance (Avoid Intersect or OrderIntersectScan).
IntersectScan – Uses more than two indexscan and does Intersect on META().id, dpeneds on data it may be costly.
OrderIntersectScan – Same as InterSectScan, In addition first IndexScan order is preserved and avoids sort.

create index myIndex on app(appId, geofenceBased, status, createdAt DESC)
WHERE deleted = FALSE AND _class = “com.push.data.entity.PushEntity”;'

create index myIndex1 on app(appId, status, createdAt DESC)
WHERE deleted = FALSE AND _class = “com.push.data.entity.PushEntity”;

create index myIndex2 on app(appId, geofenceBased, createdAt DESC)
WHERE deleted = FALSE AND _class = “com.push.data.entity.PushEntity”;
1 Like
3

Thanks for the reply.

4

Another option is single index and covered query + non covered query

create index myIndex on app(appId, createdAt DESC, geofenceBased, status)
WHERE deleted = FALSE AND _class = "com.push.data.entity.PushEntity";

SELECT
      META(d1).id AS _ID,
      META(d1).cas AS _CAS,
      d1.*
FROM appconnect AS d1
USE KEYS (SELECT RAW META(d).id
          FROM appconnect AS d
          WHERE (d.appId= "whitelabel" AND d.geofenceBased = false AND d.deleted= FALSE)
                        AND d._class= "com.commencis.appconnect.push.data.entity.PushEntity"
          ORDER BY d.createdAt DESC
          LIMIT 25
          OFFSET 0);

Make sure USE KEYS query covered and change based on your query

OR Use covered query get the document keys and get the document through SDK get.