Moodle

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.9, 1.9.1, 1.9.2, 1.9.3, 1.9.4, 1.9.5
• Fix Version/s: None
• Component/s: Performance, Roles / Access
• Labels:

  None
• Environment:

  MySQL 5.0.51a, Ubuntu Linux,

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!

• Assignee:

  Martín Langhoff

  Reporter:

  Wen Hao Chuang

  Participants:

  Martín Langhoff, Wen Hao Chuang

• Created:

  21/Oct/09 3:23 AM

  Updated:

  21/Oct/09 3:23 AM