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 (
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!