Anyway to make this simple count + group query faster?


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


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


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

In 5.5 you can use Index Grouping and Aggregation

Pre 5.5 check technique described in this article

1 Like

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.