N1QL Array index in 6.0

Hi,

I have upgraded my CB server to 6.0CE from 4.5.1 CE. I need a best querying method for my use case using N1QL.

My document is as follows:
document id: activity:253 (253 is student id)
document content:
{
"_class" = "com.schoolapp.entity.Activity",
"studentId":253,
"classId":21,
"entryTime":1546281000000, // time when student entered in classroom
"exitTime":1546281500000, // time when student exited from classroom
"dates":[1546281000000, 1546281500000], // this array will be having entryTime and exitTime only in order
"timespend": 500000
}

There are 20M documents like this. My requirement is to get activity list of given time interval in ascending order of entry time. I can also provide studentId and classId filter if needed.

The index i am using now is as follows:
CREATE INDEX 'idx activity' ON 'activity-bucket'(all (array 'i' for 'i' in 'dates' end)),'studentId','classId') WHERE ('_class' = "com.schoolapp.entity.Activity") USING GSI;

For Querying activity whithin 1546280900000 and 1546281300000, My query is:
select meta(b).id, studentId, classId, entryTime, exitTime, timespend from 'activity-bucket' where _class = 'com.schoolapp.entity.Activity' and any i in dates satisfies i >= 1546280900000 end and any id in dates satisfies id <= 1546281300000 end offset 0 limit 4000

NB: The query should return all activities which overlaps the given time span.

But the query taking around 2minutes. sometimes it return following errors:
{
“status”: “Unexpected server error”
}
or
[
{
“code”: 5000,
“msg”: " Index scan timed out - cause: Index scan timed out",
“query_from_user”: “select meta(b).id, studentId, classId, entryTime, exitTime, timespend from ‘activity-bucket’ where any i in dates satisfies i >= 1546280900000 end and any id in dates satisfies id <= 1546281300000 end offset 0 limit 4000”
}
]

It would be great if you can suggest the best query and index i can use in this use case.

You are not required ARRAY index because you have same values in the fields. Also avoid using spaces in identifiers.

CREATE INDEX idx_activity ON `activity-bucket`(entryTime, exitTime, studentId, classId,timespend )
 WHERE _class = "com.schoolapp.entity.Activity";

SELECT META(b).id, b.studentId, b.classId, b.entryTime, b.exitTime, b.timespend
FROM `activity-bucket` AS b
WHERE b._class = "com.schoolapp.entity.Activity"
      AND b.entryTime >= 1546280900000
      AND b.exitTime <= 1546281300000
OFFSET 0
LIMIT 4000;

I think following query might be more efficient because index-scan is more precise.

 SELECT META(b).id, b.studentId, b.classId, b.entryTime, b.exitTime, b.timespend
    FROM `activity-bucket` AS b
    WHERE b._class = "com.schoolapp.entity.Activity"
          AND b.entryTime BETWEEN 1546280900000 AND 1546281300000
          AND b.exitTime  BETWEEN  1546280900000 AND 1546281300000
    OFFSET 0
    LIMIT 4000;

Thank you for your quick response @vsr1.
I have tried our current query in CB4.5.1. Its having better performance than 6.0.
In 4.5.1, array indexing had much performance than the index you mentioned. By the way, i will try the method you mentioned in 6.0 and let you know the result.

Regarding spaces in identifiers, it was a typo. Sorry.

Regarding second query, it wont completely satisfy our use case.
We need overlapping activities also. Like activity having entryTime < 1546280900000 and exitTime > 1546281300000. This query wont return such activities.

Hello @vsr1
I have tried the suggested index in 6.0 also. Still query took 2 minute to return result.
Index:
CREATE INDEX 'idx_activity' ON 'activity-bucket'('entryTime','exitTime','studentId','classId') WHERE ('_class' = "com.schoolapp.entity.Activity") USING GSI;

Query:
select meta(b).id, studentId, classId, entryTime, exitTime from 'activity-bucket' b where '_class'= 'com.schoolapp.entity.Activity' and exitTime >= 1546281000000 and entryTime <= 1548745740000 offset 0 limit 4000

I know timespend isn’t indexed. But i assume it won’t take 2 minutes for fetching 1 field extra.

It will, please index timespend without it will do Fetch. How is it possible same student exit class before entering.
2minutes is too long. After execution goto Profile Text tab in UI and take look. If needed post it.

https://blog.couchbase.com/n1ql-practical-guide-second-edition/ Check out Optimize N1QL Performance Using Request Profiling

https://blog.couchbase.com/create-right-index-get-right-performance/ If you can provide classId, studentId follow recommendation in this link

Offset is too high follow recommendation in this link https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/