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

Database query for Course Participation report can crash large sites

    XMLWordPrintable

    Details

      Description

      We have had a few large clients report severe performance degradation, including failures to load pages. The problem was traced to a database query in the Course Participation report. Example query to follow. On large sites with role overrides, this query can topple a database server.

      SELECT ra.userid, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname, u.idnumber, l.actioncount AS count
                        FROM (SELECT DISTINCT userid FROM mdl_role_assignments WHERE contextid IN ('1065965','20926','1') AND roleid = '2' ) ra
                        JOIN mdl_user u ON u.id = ra.userid
                   LEFT JOIN (
                          SELECT userid, COUNT(crud) AS actioncount
                            FROM mdl_logstore_standard_log
                           WHERE contextinstanceid = '861505'
                             AND timecreated > '1420869600' AND crud IN ('c','r','u','d')
                             AND edulevel = '2'
                             AND anonymous = 0
                             AND contextlevel = '70'
                             AND (origin = 'web' OR origin = 'ws')
                        GROUP BY userid) l ON (l.userid = ra.userid) ORDER BY lastname DESC LIMIT 0, 20
      

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                14/Sep/15