I am using Couchbase Community edition 6.5 and trying to aggregate data over multiple JSONs and between a time range as shown below:(This is just a subpart of the whole query - I have SUM aggregations on other fields as well.)
SELECT SUM(sales) as salesValue from bucketName WHERE id = 12345 AND weekStartDate BETWEEN “2020/08/13” AND “2020/08/19” GROUP BY country
But the above takes about 1 minute to return the response.
If I remove the weekStartDate range search from the above query, I get the result in few milliseconds, as shown below:
SELECT SUM(sales) as salesValue from bucketName WHERE id = 12345 GROUP BY country
I have created the following index:
CREATE INDEX adv_weekStartDate_id ON 'bucketName'('sales','weekStartDate','id')
(I have replaced " ` " with " ’ " , as was getting removed in the post )
Removing the date Range from the query gives faster result. But I want to get the results with the date Range.
How do I optimize this query to get results faster?