Limit Offset Ordering

SQL++
1

Hi All,
We have queries that we are running (fairly basic select statements), but are using LIMIT/OFFSET since the volumes we’re retrieving are too large to hold in memory for processing. Something we’ve noticed, is that our data is getting duplicated in some cases, and missing in other cases after retrieving data using LIMIT/OFFSET. Do we need to use an ORDER BY clause in order to get consistent results when using LIMIT/OFFSET?

We’re not sure this is the cause, but it seems likely - wondering if the query is using different indexes, perhaps an index we intend and then an intersection when we query a second time…which we thought might cause a different sort order, which would explain the missed/duplicated data while using LIMIT/OFFSET.

Any thoughts?

2

Without ORDER BY results can be different each time you execute (no guarantee).
If data is changing ORDER BY also can cause duplicates/skip, Also need to materialize all the qualified results (last one may be candidate first in order). This can cause higher latencies.
You mentioned queries are simple. See if you can use keyset pagination described Using OFFSET and Keyset in N1QL | The Couchbase Blog

If query using IntersectScan try to create composite index ( https://index-advisor.couchbase.com/indexadvisor/#1), if required provide USE INDEX hint.

checkout Limit, offset and order by - #2 by vsr1