Details
-
Improvement
-
Status: Closed
-
Major
-
Resolution: Won't Fix
-
2.2.2
-
None
-
MOODLE_22_STABLE
-
Description
On a large site with a significant number of category enrolled users, the following query will attempt to run multiple times over, eventually causing performance problems on the database.
SELECT e.*, ue.userid
|
FROM mdl_enrol e
|
JOIN mdl_context ctx ON (ctx.instanceid = e.courseid AND ctx.contextlevel = '50')
|
JOIN mdl_user_enrolments ue ON (ue.enrolid = e.id)
|
LEFT JOIN (SELECT DISTINCT cctx.path, ra.userid
|
FROM mdl_course_categories cc
|
JOIN mdl_context cctx ON (cctx.instanceid = cc.id AND cctx.contextlevel = '40')
|
JOIN mdl_role_assignments ra ON (ra.contextid = cctx.id AND ra.roleid IN ('5','9','11'))
|
) cat ON (ctx.path LIKE CONCAT(cat.path, '/%') AND cat.userid = ue.userid)
|
WHERE e.enrol = 'category' AND cat.userid IS NULL
|
Please optimize this query.
