Hi @tzali, great questions.
First the solution, and then a little bit on the background. For the next upcoming release (post 4.1), we have fixed the performance of ORDER BY + LIMIT. See http://review.couchbase.org/#/c/57495/
Your best pagination query would be:
SELECT …
FROM …
WHERE order_key >= prev_value /* order_key must be indexed /
ORDER BY order_key
LIMIT page_size / page_size should be less than 8k or some configurable value */
If you are touching a small number of fields (say 8 or less, e.g. type, id, fname, lname, order_key), you should consider a covering index (a composite index that starts with order_key and contains the other fields).
Next, some background:
N1QL uses a lot of concurrency internally. In the case of your LIMIT 1 query, or any query without an explicit ORDER BY, the concurrent processing means that the results will be returned in a random, unstable order. There is a setting in the N1QL REST API to override this concurrent behavior (max_parallelism) and force the results to appear in index scan order.
There are also some optimizations we can do, e.g. if you have a covering index and a LIMIT 1, we should not parallelize the processing.
But again, the fix for ORDER BY + LIMIT should resolve the issue in your case.
Thank you.