We have had a few data requests, and a query is getting produced in the class core_grade\privacy\provider which locks our MySQL database. The query looks like the following in the cron log:
SELECT ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
FROM mdl_context ctx
JOIN (
SELECT DISTINCT ctx.id
FROM mdl_grade_items gi
JOIN mdl_context ctx
ON ctx.instanceid = gi.courseid
AND ctx.contextlevel = ?
LEFT JOIN mdl_grade_grades gg
ON gg.itemid = gi.id
AND (gg.userid = ? OR gg.usermodified = ?)
LEFT JOIN mdl_grade_grades_history ggh
ON ggh.itemid = gi.id
AND (
ggh.userid = ?
OR ggh.loggeduser = ?
OR ggh.usermodified = ?
)
WHERE gg.id IS NOT NULL
OR ggh.id IS NOT NULL) target ON ctx.id = target.id
[array (
0 => 50,
1 => 3,
2 => 3,
3 => 3,
4 => 3,
5 => 3,
)]
In a small database, this isn't a problem, and the query completes in a reasonable amount of time, however, in our large database (2+ million rows in the mdl_grade_grades_history table), the query can never complete, and it locks up our MySQL server. Has anyone run into this issue before?
We are running Moodle in a corporate training environment, and have our grade history set to 1000 days, which I realize could likely be lowered. However, the people who make the decisions want to keep the historical data in Moodle so that students can access it. We are currently using Moodle 3.5.2.
