N1QL Query Timeout Problem

SQL++
1

My problem is that the following query does not return any result due to timeout exception:

SELECT Event.Name, count (*) as NumEvent FROM test-couchbase-ale WHERE StartTimeUtc BETWEEN ‘2017-02-01T00:00:00Z’ AND ‘2017-03-01T00:00:00Z’ GROUP BY Event.Name ORDER BY NumEvent DESC;

I have tried to increase query timeout and i have also created an index on StartTimeUtc but I have not resolved the problem.

2

You might have lot of entries and group and aggregate takes time.

try with

CREATE INDEX ix1 ON `test-couchbase-ale` (`StartTimeUtc`, Event.Name);
SELECT Event.Name, count (1) as NumEvent 
FROM test-couchbase-ale WHERE StartTimeUtc BETWEEN ‘2017-02-01T00:00:00Z’ AND ‘2017-03-01T00:00:00Z’ 
GROUP BY Event.Name ORDER BY NumEvent DESC;

I have tried to increase query timeout and i
Also checkout https://dzone.com/articles/count-amp-group-faster-using-n1ql

3

CREATE INDEX eventNameData ON test-couchbase-ale((Event.Name),StartTimeUtc) WHERE ((Event.Name) is not missing;
has this definition the same effect ?

4

Not same effect. Index Scans are range based and your index produces lot of false positives and query will eliminate them.
Please checkout https://dzone.com/articles/designing-index-for-query-in-couchbase-n1ql

5

In that link i read this: “In the case of a composite key, if leading keys predicates are non-equality, the indexer will produce false positives” (I think it is you were talking about). What does it means “leading keys predicates are non-equality”? Equality in term of what?

6

Equality means predicate of
a = 5
a IN [ 1, 2, 3]
non-equality means > , >=, <, <= , between , NOT, LIKE,…

Couchbase scans are range scans. In 5.0.0 this has been improved and indexer applies composite key parts individually. You can also try 5.0.0

7

So If I understood well, is the following index good for the query?

CREATE INDEX idx_2
ON test-couchbase-ale
( StartTimeUtc, DISTINCT ARRAY S.Value FOR S IN Signals END,Consist )

SELECT T.Consist, count(*) AS NUM_ALERT FROM test-couchbase-ale AS T UNNEST T.Signals as S WHERE S.Value>100 AND T.StartTimeUtc BETWEEN ‘2013-12-01T00:00:00Z’ AND ‘2013-12-02T00:00:00Z’ GROUP BY T.Consist ORDER BY NUM_ALERT;

8

If you are counting number of array values > 100 then you can do this.

CREATE INDEX ix2 ON `test-couchbase-ale` (`StartTimeUtc`, Consist, Signals);
SELECT T.Consist, 
               SUM ( ARRAY_COUNT(ARRAY 1 FOR v  IN  T.Signals WHEN v.`Value` > 100 END))  AS NUM_ALERT
FROM `test-couchbase-ale`  AS T 
WHERE T.StartTimeUtc BETWEEN "2013-12-01T00:00:00Z" AND "2013-12-02T00:00:00Z"
GROUP BY T.Consist
 ORDER BY NUM_ALERT;

If Signals array is too big, make it non covered index CREATE INDEX ix2 ON test-couchbase-ale (StartTimeUtc, Consist); Otherwise indexer will not index document.

9

Thank you so much for the support!