Index Creation Possibility with Sorted Data based on Some Field

Couchbase Server
1

Hi @vsr1 ,

Is there a way to create the index based on some field that is order by desc.
I don’t wish to add the order by in the query.
{
“name” : “value”,
“lastModified” : 1633593117523,
“type” : “group”
}

{
“name” : “value”,
lastModified : 1633593117599,
“type” : “group”
}

Sample:
select * from bucket as doc where doc.name = "value " where doc.type = “group” order by doc.lastModified desc

Current Index
create index ‘value’ ON bucket (‘name’, ‘lastModified’) where type = “group”

Desired State

Query : select * from bucket as doc where doc.name = "value " where type = “group”
Index : create index ‘value’ ON bucket (‘name’, ‘lastModified desc’) where type = “group” // something like this

Thanks
Harinder

2

its same syntax like i have studied in my 98-364 - Database Fundamentals exams and MYSQL professional exams.

3

hi @J.donald ,

I had tried this the desired way in couch-base n1ql , but it doesn’t work , while querying it is not giving the correct order.

4 
select * 
from bucket as doc 
where doc.name = "value " where doc.type = “group” 
order by doc.lastModified desc
create index ix1ON `bucket` (name, lastModified DESC ) where type = “group”;

https://index-advisor.couchbase.com/indexadvisor/#1

5

Is it mandatory for this to work giving the order by in query as well ?

6

If your query requires ORDER BY, You must provide ORDER BY otherwise there is no guarantee (Sort is expensive operation. If user not required and doing one take out all resources and impact latency).
Based on query if it can utilize index order it will avoid sort (Can be seen in EXPLAIN missing Order operator at the end). If it can’t use index order it will do explicit sort.
Without Query ORDER BY, results can be in sorted order of index some cases, but it is no guarantee.

Also check out Appendix of Using OFFSET and Keyset in N1QL | The Couchbase Blog when query can use index order.

1 Like
7

i also used dumpscollection.com for certification exam practice.