[SOLVED] Couchbase metrics sortcount always zero

Couchbase metrics sortcount always zero - N1QL + .NET SDK

I need the total no. of records available for the condition to display like - "Page 1 of 'Total Records'" (I'm displaying data in a grid). According to Couchbase forums, sort count metrics should give me the total records but it gives me zero. Any help is highly appreciated. using the .NET SDK.

N1QL query used:

        select  Title
        from `CMS`
        where LastUpdatedOn > 0 and IsLatestVersion = true   
        order by LastUpdatedOn DESC LIMIT 2

Index used:

    CREATE INDEX `CMS_idx_LastUpdatedOnDesc_IsLatestVersion` ON `CMS`(LastUpdatedOn DESC, IsLatestVersion);

Solution

The query ORDER BY clause follows index key order. So query uses Index order and avoids sort and provide the results quickly. When that happens sortCount in metrics will be missing (which will be zero 0). As it uses index order it terminates index scan once it satisfies query. So the total qualified records are unknown. You need to issue explicit count query to know total results.

Even if query does explicit sort with LIMIT + OFFSET (<= 8192), It will use TOP n sort. In that case also sortCount may not reflect right vlaue.

The following index can perform better. The query recognizes the IsLatestVersion equality predicate and still uses index order. This avoids more index scan pages (similar like btree)

CREATE INDEX `CMS_idx_LastUpdatedOnDesc_IsLatestVersion` ON 
  `CMS`(IsLatestVersion, LastUpdatedOn DESC, Title); 

SELECT  Title
FROM `CMS`
WHERE LastUpdatedOn > 0 and IsLatestVersion = true   
ORDER BY LastUpdatedOn DESC 
LIMIT 2

https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/