Index for N1QL Query with "order by ... DESC"

SQL++
, ,
1

Hi all,

I’m facing timeout issues when running a SELECT query with N1QL. The query looks as follows: SELECT endDate, name FROM bucket.scope.collection ORDER BY endDate DESC LIMIT 1. I.e., I want to obtain the name of the last element.

I have tried different indexes without success. When I use the Index Advisor, it is using the PRIMARY INDEX and not suggesting any additional indexes. However, in a collection with 600k items, it times out regularly after five minutes.

Do you have any suggestions how to improve the query or my indexes?

Thanks for your help in advance :pray:t3:

2 

CREATE INDEX ix1 ON bucket.scope.collection (endDate DESC, name);


SELECT endDate, name 
FROM bucket.scope.collection AS c
WHERE endDate IS NOT MISSING
ORDER BY endDate DESC
 LIMIT 1
1 Like