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.