For more details please see discussion at http://moodle.org/mod/forum/discuss.php?d=135455

Basically, on a larger site, we found that this query appears in the MySQL slow query log:

SELECT id, firstname, lastname, email FROM mdl_user WHERE username <> 'guest' AND deleted = 0 AND confirmed = 1 AND id NOT IN ( SELECT u.id FROM mdl_role_assignments r, mdl_user u WHERE r.contextid = xxxx AND u.id = r.userid AND r.roleid = 3 ) ORDER BY lastname ASC, firstname ASC;

Based on our system administrator, during a 15 minutes interval they can see this query between 5 - 20 times. Each time this query would take around 2 - 3 seconds to complete. This query seems to be triggered when assigning a new teacher on the "Assign roles" page.

Eloy pointed out (in the forum discussion) that the "IN is very evil. He came up with a new query which seems to be running at least 18% faster:


SELECT id, firstname, lastname, email
FROM mdl_user u
WHERE username <> 'guest'
  AND deleted = 0
  AND confirmed = 1
  AND NOT EXISTS (
      SELECT 'x'
      FROM mdl_role_assignments r
      WHERE r.contextid = xxxx
        AND r.roleid = yyyy
        AND r.userid = u.id)
ORDER BY lastname ASC, firstname ASC;

Would be nice if this performance issue can be resolved in the CORE. Thanks!