Paginating with n1ql

SQL++
21

Hi @geraldss,

Thank you for reply. But in all of my above 3 queries I have used ORDER BY -_createdAt. But I am missing sort count in Q1 only

SELECT meta().id FROM cms WHERE -_createdAt < 0 AND _type=‘Product’ AND _active=true ORDER BY -_createdAt LIMIT 100 OFFSET 0;

22

Adding to above statement

I change the create index statement . And now I am getting sort Count , but the query execution time is very high ( about 10sec )
Old Index

CREATE INDEX prod_created_idx ON cms((-_createdAt),_type,_active) WHERE ((_type = “Product”) and (_active = true));

New index
CREATE INDEX prod_created_idx ON cms(_type,_active,(-_createdAt)) WHERE ((_type = “Product”) and (_active = true));

Query remains same

SELECT meta().id FROM cms WHERE _type = “Product” AND _active = TRUE AND -_createdAt < 0 ORDER BY -_createdAt LIMIT 100 OFFSET 0;

23

You have 2 options:

(1) Use index order for better performance, and use a separate query to get the count

(2) Do not use index order, and let the query engine do the sort. This will incur a performance penalty, but you will get the sortCount.

Those are your two options.

24

Hi @geraldss, I’m facing similar issue as well here. Are we still stuck with these two and only options? If there’s some other advancement which can make things more optimized in new releases which I’m not aware of, then please let me know.

25

Hi @krishan.jangid,

Check blog from @keshav_m https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/

Also you can create DESC collation index.

If still have questions post as separate post.