-
Bug
-
Resolution: Fixed
-
Major
-
2.9.6, 3.0.4
-
MOODLE_29_STABLE, MOODLE_30_STABLE
-
MOODLE_30_STABLE, MOODLE_31_STABLE
-
load_course_context currently does DB queries like
SELECT ctx.path, rc.roleid, rc.capability, rc.permission |
FROM mdl_role_capabilities rc |
JOIN mdl_context ctx ON (ctx.id = rc.contextid) |
JOIN mdl_context cctx ON (cctx.id = 1490797 AND (ctx.id IN (1490797,1479196,160502,160501,1) OR ctx.path LIKE '' || cctx.path || '/%' )) |
WHERE rc.roleid IN (14,5,7) |
ORDER BY rc.capability |
which the Postgres query handler, at least, handles badly (0.7 seconds on our site). It does a table scan of mdl_context ctx (1.5 million rows).
In the case where the query is WHERE rc.roleid =7, it does much better (15ms) with a completely different query plan.
However, the code where this query is executed already has the full course context object (cctx), so we know the path and therefore you can construc the equivalen, but simpler query
SELECT ctx.path, rc.roleid, rc.capability, rc.permission |
FROM mdl_context ctx |
JOIN mdl_role_capabilities rc ON rc.contextid = ctx.id |
WHERE rc.roleid IN (14,5,7) |
AND (ctx.id IN (1490797,1479196,160502,160501,1) |
OR ctx.path LIKE '/1/160501/160502/1479196/1490797/%') |
ORDER BY rc.capability |
That then executes in 15ms, returning the same data as before.
Patch coming up.
- has been marked as being related by
-
MDL-49398 Role definition caching & accesslib refactoring
-
- Closed
-