N1QL query with Order by has response issue

SQL++
1

Hi Team,

can you please help on below. we have below query with “order by createDate desc”
without it below index work with order by it timeout and index is not scanned properly.
INDEX:
CREATE INDEX idx_relatesToDomain_stateSpecId_productinstance ON optima((self.type),(self.relatesToDomain),(currentState.stateSpecId)) WHERE (type = “processinstance”)

QUERY: “order by createDate desc” without order by works with order by timeout.
SELECT * FROM optima WHERE type = ‘processinstance’ and relatesToDomain == 1 and currentState.stateSpecId != 2000 order by createDate desc LIMIT 24 OFFSET 0

2 
CREATE INDEX ix1 ON optima(relatesToDomain, createDate DESC, currentState.stateSpecId) WHERE type = "processinstance";
SELECT *
FROM optima
WHERE type = "processinstance"
      AND relatesToDomain == 1
      AND currentState.stateSpecId != 2000
      AND createDate IS NOT NULL
ORDER BY createDate DESC
LIMIT 24
OFFSET 0

SELECT *
FROM optima USE KEYS (SELECT RAW META().id FROM optima
                      WHERE type = "processinstance"
                            AND relatesToDomain == 1
                            AND currentState.stateSpecId != 2000
                            AND createDate IS NOT NULL
                      ORDER BY createDate DESC
                      LIMIT 24 )
ORDER BY createDate DESC;


3

Thanks i tried the index proposed with not null it works.

4

@mohamedw,

https://query-tutorial.couchbase.com/tutorial/#55

In above link and modify the query and run. It gives recommendation and try out.

5

Thanks will give it a try