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

            marina Marina Glancy
            cfollin Chris Follin
            Rajesh Taneja Rajesh Taneja
            Dan Poltawski Dan Poltawski
            Mark Nelson Mark Nelson
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Clockify

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.