We have noticed poor performance on my/courses.php.
For users with a moderate amount of courses (<100) the My Courses page loads quickly as it should.
For users with a lot of courses (>200) this is causing waiting times of several minutes on my/courses.php.
This is probably a regression introduced by MDL-76174, the SQL-query in course/classes/category.php, function get_nearest_editable_subcategory. Unfortunately MDL-78618 didn't fix this issue.
Apparently misho was reporting the same problem and suggesting a patch asking for review in MDL-78618, but unfortunately didn't get around to reporting a new issue.
https://tracker.moodle.org/browse/MDL-78618?focusedId=1032741&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-1032741
The patch can be found here: https://tracker.moodle.org/secure/attachment/141426/141426_alternative-to-union-clause.patch
Could you please have a look at this?
On our site, there are 2159146 rows in mdl_context, 2291752 in mdl_role_assignments, 11068 in mdl_role_capabilities and 74 in mdl_course_categories.
The user in question (userid = 1234) has 197 rows in mdl_role_assignments.
Our database is PostgreSQL 15.
The following query from course/classes/category.php function get_nearest_editable_subcategory takes 40s, returning 0 rows.
In the second part of the SQL query there is a JOIN of five tables (mdl_course_categories, mdl_context, mdl_role_capabilities, mdl_role_assignments, and mdl_context again). This is very heavy to process for users with many rows in mdl_role_assignments.
SELECT cc.id,cc.name,cc.idnumber,cc.parent,cc.sortorder,cc.coursecount,cc.visible,cc.depth,cc.path, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked |
FROM mdl_course_categories cc |
JOIN mdl_context ctx ON cc.id = ctx.instanceid AND ctx.contextlevel = '40' |
JOIN mdl_role_assignments ra ON ra.contextid = ctx.id |
WHERE ctx.path LIKE '/1/%' |
AND ra.userid = '1234' |
UNION
|
SELECT cc.id,cc.name,cc.idnumber,cc.parent,cc.sortorder,cc.coursecount,cc.visible,cc.depth,cc.path, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked |
FROM mdl_course_categories cc |
JOIN mdl_context ctx ON cc.id = ctx.instanceid AND ctx.contextlevel = '40' |
JOIN mdl_role_capabilities rc ON rc.contextid = ctx.id |
JOIN mdl_role_assignments rc_ra ON rc_ra.roleid = rc.roleid |
JOIN mdl_context rc_ra_ctx ON rc_ra_ctx.id = rc_ra.contextid |
WHERE ctx.path LIKE '/1/%' |
AND rc_ra.userid = '1234' |
AND (ctx.path = rc_ra_ctx.path OR ctx.path LIKE CONCAT(rc_ra_ctx.path, '/%')); |
The corresponding query in the patch suggested by misho completes in a fraction of a second, returning 0 rows.
SELECT cc.id,cc.name,cc.idnumber,cc.parent,cc.sortorder,cc.coursecount,cc.visible,cc.depth,cc.path, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked |
FROM mdl_course_categories cc |
JOIN mdl_context ctx ON cc.id = ctx.instanceid AND ctx.contextlevel = '40' |
WHERE ctx.path LIKE '/1/%' |
AND ( |
(ctx.id IN ( |
SELECT ra.contextid |
FROM mdl_role_assignments ra |
WHERE ra.userid = '1234' |
))
|
OR
|
(ctx.id IN ( |
SELECT rc.contextid |
FROM mdl_role_capabilities rc |
JOIN mdl_role_assignments rc_ra ON rc.roleid = rc_ra.roleid |
JOIN mdl_context rc_ra_ctx ON rc_ra_ctx.id = rc_ra.contextid |
WHERE rc_ra.userid = '1234' |
AND (ctx.path = rc_ra_ctx.path OR ctx.path LIKE CONCAT(rc_ra_ctx.path, '/%'))))); |
- has a non-specific relationship to
-
MDL-78370 Course Overview Block Performance
-
- Closed
-
- is a regression caused by
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
- is duplicated by
-
MDL-81692 Improved performance for "my courses"
-
- Closed
-