Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-20611

"Assign roles" page sql query can use some improvement (query went into slow query log for larger sites)

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Won't Fix
    • 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,
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_19_STABLE

      Description

      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!

        Attachments

          Activity

            People

            Assignee:
            martinlanghoff Martín Langhoff
            Reporter:
            chuang Wen Hao Chuang
            Participants:
            Component watchers:
            Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Sara Arjona (@sarjona)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: