-
Bug
-
Resolution: Duplicate
-
Minor
-
None
-
3.0.6
-
None
-
MOODLE_30_STABLE
We have had reports from some of our users about how slow Moodle was. Looking in my mysql slow query log I found a query was appearing time again which was taking in excess of 6 seconds to run.
An example of this query is attached, along with an explain which shows that it is doing a full table scan instead of using the available indexes.
I have traced this query to /lib/accesslib.php and the function get_user_access_sitewide():
foreach ($raparents as $roleid=>$ras) { |
$cp++; |
list($sqlcids, $cids) = $DB->get_in_or_equal($ras, SQL_PARAMS_NAMED, 'c'.$cp.'_'); |
$params = array_merge($params, $cids); |
$params['r'.$cp] = $roleid; |
$sqls[] = "(SELECT ctx.path, rc.roleid, rc.capability, rc.permission |
FROM {role_capabilities} rc
|
JOIN {context} ctx
|
ON (ctx.id = rc.contextid)
|
JOIN {context} pctx
|
ON (pctx.id $sqlcids |
AND (ctx.id = pctx.id
|
OR ctx.path LIKE ".$DB->sql_concat('pctx.path',"'/%'")." |
OR pctx.path LIKE ".$DB->sql_concat('ctx.path',"'/%'").")) |
LEFT JOIN {block_instances} bi
|
ON (ctx.contextlevel = ".CONTEXT_BLOCK." AND bi.id = ctx.instanceid) |
LEFT JOIN {context} bpctx
|
ON (bpctx.id = bi.parentcontextid)
|
WHERE rc.roleid = :r{$cp} |
AND (ctx.contextlevel <= ".CONTEXT_COURSE." OR bpctx.contextlevel < ".CONTEXT_COURSE.") |
)";
|
}
|
|
|
// fixed capability order is necessary for rdef dedupe |
$rs = $DB->get_recordset_sql(implode("\nUNION\n", $sqls). "ORDER BY capability", $params); |
Looking in the tracker I have found an issue that touches on this, but in the end was rejected (MDL-49398).
I'd really appreciate bigger brains than mine looking at this one, as I'm not sure how this part of Moodle hangs together.
I should perhaps add that we make extensive use of meta course enrolments which might be affecting this. I've tried to slim these down, but we still have a lot, and this number will continue to grow because we add new courses for each academic year.
I've also boosted my join_buffers to help ameliorate this situation, but I think the full table scan is killing any benefit here.
