Indexing start and end time

SQL++
1

Hi all,

I would like to query:

SELECT META(app).id AS _ID, META(app).cas AS _CAS, app.*
FROM app where _class = “com.inappmessage.data.model.InappMessageEntity”
and meta().id is not missing and appId = “whitelabel” and (schedule.manualStop = true or schedule.end > “2019-02-21T13:02:10.296Z[UTC]”)
and (schedule.start < “2019-02-21T13:02:10.296Z[UTC]” or schedule.immediate = true) and deleted = false and status = “LIVE” and channel = “WEB”
like this.

My index is :

CREATE INDEX inapp_message_schedule_interval_idx ON app(str_to_millis((schedule.startTime)),str_to_millis((schedule.endTime))) WHERE (_class = “com.inapp_message.data.model.InappMessageEntity”)

like this but it does not use my index. How can I achieve this ?

2 
CREATE INDEX ix1 app(appId, deleted, status, channel,
                  (CASE WHEN schedule.manualStop = true THEN "9999-12-31" ELSE schedule.end END),
                  (CASE WHEN schedule.immediate = true THEN "0001-01-01" ELSE schedule.start END))
WHERE (_class = "com.inapp_message.data.model.InappMessageEntity");

SELECT META(app).id AS _ID, META(app).cas AS _CAS, app.*
FROM app
WHERE _class = "com.inappmessage.data.model.InappMessageEntity"
      AND appId = "whitelabel" AND deleted = false AND status = "LIVE" AND channel = "WEB"
      AND (CASE WHEN schedule.manualStop = true THEN "9999-12-31" ELSE schedule.end END) > "2019-02-21T13:02:10.296Z"
      AND (CASE WHEN schedule.immediate = true THEN "0001-01-01" ELSE schedule.start END) < "2019-02-21T13:02:10.296Z";

OR

CREATE INDEX ix2 app(appId, deleted, status, channel,schedule.start, schedule.end, schedule.manualStop, schedule.immediate)
    WHERE (_class = "com.inapp_message.data.model.InappMessageEntity");

    SELECT META(app).id AS _ID, META(app).cas AS _CAS, app.*
    FROM app
    WHERE _class = "com.inappmessage.data.model.InappMessageEntity"
          AND appId = "whitelabel" AND deleted = false AND status = "LIVE" AND channel = "WEB"
          AND (schedule.manualStop = true OR schedule.end > "2019-02-21T13:02:10.296Z")
          AND (schedule.immediate = true OR schedule.start < "2019-02-21T13:02:10.296Z");

Check out https://blog.couchbase.com/n1ql-practical-guide-second-edition/
https://blog.couchbase.com/create-right-index-get-right-performance/

1 Like