MongoDB seems very slow when adding a $group
pipeline to simply count the number of records for each occurrence. Below is an example of running it without $group
, using $lookup
perform a join. This runs in less than 1 second.
Note: I'm doing the lookup so I can eventually count the attendance code type for each student as well as the total
db.attendances.aggregate(
[
{
$lookup: {
from: 'attendance_codes',
localField: 'attendance_code',
foreignField: '_id',
as: 'attendance_code'
}
},
{
$unwind: '$attendance_code'
}
]
)
Adding in the $group
step to simply group by the student and count the number of records causes this to exceed my maximum execution time of 30 seconds.
db.session_attendances.aggregate(
[
{
$lookup: {
from: 'attendance_codes',
localField: 'attendance_code',
foreignField: '_id',
as: 'attendance_code'
}
},
{
$unwind: '$attendance_code'
},
{
$group : {
_id: {
student: "$student"
},
count:{ $sum:1 }
}
}
]
)
Below are examples of each table
Example Attendance record
Table has around 650,000 records
{
"_id" : ObjectId("object id")
"attendance_code" : ObjectId("foreign object id"), (is indexed)
"date" : "2018-09-02 00:00:00.000000",
"student" : ObjectId("foreign object id"), (is indexed)
"created_at" : ISODate("2019-04-01T09:32:15.776Z"),
"updated_at" : ISODate("2019-07-02T08:15:07.096Z")
}
Example Attendance Code record
Table has around 25 records
{
"_id" : ObjectId("object id"),
"code" : "/",
"created_at" : ISODate("2019-04-01T09:32:13.602Z"),
"type" : "PRESENT",
"updated_at" : ISODate("2019-08-07T20:31:43.426Z")
}