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)
"_class" = "com.schoolapp.entity.Activity",
"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
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
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”
“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.