XMLWordPrintable

    Details

    • Type: Sub-task
    • Status: Closed
    • Priority: Critical
    • Resolution: Duplicate
    • Affects Version/s: 1.8.4, 1.9
    • Fix Version/s: 1.9
    • Component/s: Administration
    • Labels:
      None
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_18_STABLE, MOODLE_19_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE

      Description

      I have a site where the statistics are taking 18 hours to run (and then you still can't see any results, see MDL-11953).

      There are lots of queries like this:

      SELECT userid, primaryrole FROM (
      SELECT active_course_users.userid,
      (SELECT roleid FROM mdl_role_assignments outer_r_a INNER JOIN mdl_role outer_r ON outer_r_a.roleid=outer_r.id
      INNER JOIN mdl_context c ON outer_r_a.contextid = c.id
      WHERE c.instanceid=4 AND c.contextlevel = 50 AND outer_r_a.userid=active_course_users.userid
      AND NOT EXISTS (SELECT 1 FROM mdl_role_assignments inner_r_a
      INNER JOIN mdl_role inner_r ON inner_r_a.roleid = inner_r.id
      WHERE inner_r.sortorder < outer_r.sortorder
      AND inner_r_a.userid = outer_r_a.userid
      AND inner_r_a.contextid = outer_r_a.contextid
      )
      ) AS primaryrole
      FROM (SELECT DISTINCT userid FROM mdl_log l WHERE course=4 AND (l.time > 1193702400 AND l.time < 1193788800) )
      active_course_users
      ) foo WHERE primaryrole IS NOT NULL

      and

      SELECT COUNT(DISTINCT l.id) FROM (SELECT ra.userid,
      ra.roleid AS primary_roleid,
      ra.contextid,
      r.sortorder,
      r.name,
      r.description,
      r.shortname,
      c.instanceid AS courseid,
      c.contextlevel
      FROM mdl_role_assignments ra
      INNER JOIN mdl_role r ON ra.roleid = r.id
      INNER JOIN mdl_context c ON ra.contextid = c.id
      WHERE NOT EXISTS (
      SELECT 1
      FROM mdl_role_assignments i_ra
      INNER JOIN mdl_role i_r ON i_ra.roleid = i_r.id
      WHERE ra.userid = i_ra.userid AND
      ra.contextid = i_ra.contextid AND
      i_r.sortorder < r.sortorder
      ) ) prs
      INNER JOIN mdl_log l ON (prs.userid=l.userid AND l.course=prs.courseid)
      WHERE prs.primary_roleid=3 AND prs.courseid=7165
      AND prs.contextlevel = 50 AND (l.time > 1193702400 AND l.time < 1193788800) AND l.action IN ('add','delete','edit','add mod','delete mod','edit sectionenrol','loginas','new','unenrol','update','update mod','upload','choose','choose again','add discussion','add post','delete discussion','delete post','move discussion','prune post','update post','add entry','update entry','update feedback','attempt','editquestions','review','submit','agree','assess','comment','grade','newattachment','removeattachments','resubmit','talk','add category','add comment','approve entry','delete category','delete comment','delete entry','edit category','update comment','open','set up','end','start','update grade attempt','hack','record delete');

      and

      SELECT COUNT(DISTINCT prs.userid) FROM (SELECT ra.userid,
      ra.roleid AS primary_roleid,
      ra.contextid,
      r.sortorder,
      r.name,
      r.description,
      r.shortname,
      c.instanceid AS courseid,
      c.contextlevel
      FROM mdl_role_assignments ra
      INNER JOIN mdl_role r ON ra.roleid = r.id
      INNER JOIN mdl_context c ON ra.contextid = c.id
      WHERE NOT EXISTS (
      SELECT 1
      FROM mdl_role_assignments i_ra
      INNER JOIN mdl_role i_r ON i_ra.roleid = i_r.id
      WHERE ra.userid = i_ra.userid AND
      ra.contextid = i_ra.contextid AND
      i_r.sortorder < r.sortorder
      ) ) prs WHERE prs.primary_roleid=3 AND prs.courseid=7165 AND prs.contextlevel = 50;

      That are taking > 4 seconds to run, creating a lot of disk-based temporary tables (even with a 256M memory buffer allocated for them)

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              martinlanghoff Martín Langhoff
              Reporter:
              dougiamas Martin Dougiamas
              Participants:
              Component watchers:
              Andrew Nicols, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              1 Vote for this issue
              Watchers:
              10 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                3/Mar/08