Slow N1ql query for search between date range

SQL++
1

Hi,

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?

2

DATE must be ISO-8601 format in the data and query https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html#date-formats

CREATE INDEX adv_weekStartDate_id ON bucketName(id, weekStartDate, country, sales);

SELECT SUM(sales) as salesValue 
from bucketName 
WHERE id = 12345 AND weekStartDate BETWEEN "2020-08-13" AND "2020-08-19" 
GROUP BY country;
3

Hi vsrt,

Thanks for the link. I have another question,

Can I query the above with epoch time ranges like this:

SELECT SUM(sales) as salesValue
from bucketName
WHERE id = 12345 AND weekStartDate BETWEEN “someEpoch” AND “someEpoch”
GROUP BY country;

Will it have better performance than the string date values, or its the same for both?

4

weekStartDate is Epoch you can use
weekStartDate BETWEEN 1234567 AND 234567;
If not there are date manipulations functions i above link and you need to convert before use.

5

Hi vsr1,

How do I create an index for just SUM aggregation without any where clause, like the below query:

SELECT SUM(sales) as salesValue from bucketName GROUP BY country;

I created Primary index, but its running slow, and I tried creating a secondary index like this:

CREATE INDEX ‘sales’ ON ‘bucketName’(‘sales’)

But still not able to query.
How do I create a secondary index for the above query.

6 
CREATE INDEX ix1 ON bucketName(country, sales);
SELECT SUM(sales) as salesValue
from bucketName
WHERE  country IS NOT NULL
GROUP BY country;
1 Like
7

Hi vsr1,
I am using couchbase community version 6.5 and trying to aggregate data over a date range. I have 3 million records in db.
I running the following query which is taking around 26 seconds to return response.

SELECT SUM(sales) as salest FROM myBucket WHERE weekStartDate >= 1568160000 AND weekEndDate <= 1599782400 group by country

How do I optimize this date range, can you suggest if this can be improved through indexing or some other way

8 
CREATE INDEX adv_weekStartDate_id ON bucketName(weekStartDate, weekEndDate, country, sales);

SELECT SUM(sales) as salesValue 
from bucketName 
WHERE  weekStartDate >= 1568160000 AND weekEndDate <= 1599782400
GROUP BY country;
9

Even after creating the above index, query is taking 17 seconds