Anyway to make this simple count + group query faster?

SQL++
1

Hi,

I have a fairly simple query that counts and groups as follow.

Query

select count(form) as total, form from bucketName where username='test' and form is not null group by form

Index

CREATE INDEX idx_forms_by_username ONbucketName(username,form) WHERE (formis not missing)

The query runs pretty fast for those users with few documents.

The query takes 800ms+ to return the following result. It looks like the query slows down as the # of docs increases. I also suspect that the query is causing scan backfill in the query server (Iā€™m not 100% sure yet though). Is there a way to improve the query other than using view?

[
  {
    "form": "creative",
    "total": 2900
  },
  {
    "form": "user",
    "total": 1
  },
  {
    "form": "comment",
    "total": 89050
  }
]
2

In 5.5 you can use Index Grouping and Aggregation
https://blog.couchbase.com/understanding-index-grouping-aggregation-couchbase-n1ql-query/
https://blog.couchbase.com/a-guide-to-n1ql-features-in-couchbase-5-5-special-edition/

Pre 5.5 check technique described in this article https://dzone.com/articles/count-amp-group-faster-using-n1ql

1 Like
3

Thank you.

I think my index and query are exactly same format as one that is described in pre 5.5 article.

I think we have to upgrade to 5.5 soon.