N1QL query default order with offset and limit

Hello,

I have the below query running

select * from <bucket> where type = <doc_type> and <field_name> = <value>
ORDER by meta().id
OFFSET 1000 LIMIT 100

This query is taking few secs to execute and if I remove the order by clause, the same query takes a few milliseconds to run. See below:

select * from <bucket> where type = <doc_type> and <field_name> = <value>
OFFSET 1000 LIMIT 100

I would want to use order by to make sure whenever I do offset I am slicing records that were returned in the same order.
Question: If I run the query without order by, does Couchbase returns the records in a specific order?
What do you suggest?

PS: Logically I don’t need an order. Only reason I am ordering is to make sure before doing an offset records are ordered.

Without an order-by clause the order is not guaranteed.
You may find that including meta().id in your index offers you the needed performance:

CREATE INDEX ix1 ON <bucket>(type, <field_name>, meta().id)

as this should allow the index to provide the ordering (and therefore offset + limit) before any documents are fetched.

Alternatively you may consider the form:

SELECT *
FROM <bucket> USE KEYS (SELECT RAW meta().id 
                        FROM ... 
                        WHERE ... 
                        ORDER BY ... 
                        OFFSET... 
                        LIMIT)

so that the ordering, offset & limit can be done before any documents are fetched.

HTH.

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.