Details
-
Bug
-
Status: Closed
-
Minor
-
Resolution: Fixed
-
3.3.4, 3.4.1, 3.4.6, 3.5.2, 3.6, 3.7
-
MOODLE_33_STABLE, MOODLE_34_STABLE, MOODLE_35_STABLE, MOODLE_36_STABLE, MOODLE_37_STABLE
-
MOODLE_35_STABLE, MOODLE_36_STABLE
-
Description
We detected this monitoring our live servers. The query from a call like
$groups = groups_get_all_groups($this->get_course()->id, 0, $grouping);
|
$users = get_users_by_capability($context,
|
'mod/forumng:viewdiscussion', 'u.id', 'u.lastaccess DESC', '', 200000,
|
array_keys($groups), '', 0, 0, true)
|
is running in ~7 seconds. A simple re-write of the SQL can bring that down to ~0.2 seconds. This is on our Postgres server. However, I cannot yet see how to do thatrewrite in genereral in the get_users_by_capability PHP code. I am just making this issue to log my findings.
The SQL that Moodle generates looks like:
SELECT u.id |
FROM mdl_user u |
JOIN (SELECT DISTINCT userid FROM ( |
/* Subquery. wich may be a UNION, to do the has-capability check */ |
) ra ON ra.userid = u.id |
|
|
|
|
WHERE u.deleted = 0 AND u.id <> 1 |
/* This is the problem: group membership test done as a Subquery */
|
AND (u.id IN ( |
SELECT userid |
FROM mdl_groups_members gm |
WHERE gm.groupid IN (/* List of group ids */) |
) OR u.id IN (/* List of access all groups user ids */) |
)
|
ORDER BY u.lastaccess DESC LIMIT 200000 |
Changing the group-membership test to a JOIN is much faster, however, it requires an extra DISTINCT (or it could be a GROUP BY) which does not interact well with the default ORDER BY.
SELECT DISTINCT u.id, u.lastaccess |
FROM mdl_user u |
JOIN (SELECT DISTINCT userid FROM ( |
/* Subquery. wich may be a UNION, to do the has-capability check */ |
) ra ON ra.userid = u.id |
/* Now a JOIN, with simplified WHERE, but note the change to the first line above. */
|
LEFT JOIN mdl_groups_members gm ON gm.userid = u.id |
|
|
WHERE u.deleted = 0 AND u.id <> 1 |
AND (gm.groupid IN (/* List of group ids */) |
) OR u.id IN (/* List of access all groups user ids */) |
)
|
ORDER BY u.lastaccess DESC LIMIT 200000 |