MDL-47426 assign local roles: rewrite query for better performance.
authorTim Hunt <T.J.Hunt@open.ac.uk>
Thu, 25 Sep 2014 14:52:00 +0000 (15:52 +0100)
committerTim Hunt <T.J.Hunt@open.ac.uk>
Thu, 2 Oct 2014 16:10:40 +0000 (17:10 +0100)
This is an extremely dangerous query, because it includes the user
table twice, along-side two other potentially large tables,
role_assignments and user_enrolments.

The solution is to rewrite the query so that:

1. The subquery is JOINed, not WHERE ... INed. Typically query
   optimisers handle the JOIN case better.

2. Before the join was role-assignments <-> users <-> subquery.
   That is, everything was linked to u.id.

   Now the linking is role-assignments <-> subquery <-> users,
   so the SELECT DISTINT eu1_u.id FROM {enrolled users} is central.
   That seems to send a strong hint to the query optimiser about
   a good order to execute the query.

admin/roles/classes/potential_assignees_below_course.php

index 71664a1..ce60366 100644 (file)
@@ -48,11 +48,12 @@ class core_role_potential_assignees_below_course extends core_role_assign_user_s
         $fields      = 'SELECT ' . $this->required_fields_sql('u');
         $countfields = 'SELECT COUNT(u.id)';
 
-        $sql   = " FROM {user} u
-              LEFT JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.roleid = :roleid AND ra.contextid = :contextid)
-                  WHERE u.id IN ($enrolsql)
-                        $wherecondition
-                        AND ra.id IS NULL";
+        $sql   = " FROM ($enrolsql) enrolled_users_view
+                   JOIN {user} u ON u.id = enrolled_users_view.id
+              LEFT JOIN {role_assignments} ra ON (ra.userid = enrolled_users_view.id AND
+                                            ra.roleid = :roleid AND ra.contextid = :contextid)
+                  WHERE ra.id IS NULL
+                        $wherecondition";
         $params['contextid'] = $this->context->id;
         $params['roleid'] = $this->roleid;