Moodle

• 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

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)

1. 

   stats-take-1.patch

   28/Jan/08 8:36 AM

   20 kB

   Martín Langhoff

• Assignee:

  Martín Langhoff

  Reporter:

  Martin Dougiamas

  Participants:

  Martin Dougiamas, Martín Langhoff, Petr Škoda (skodak), Sam Marshall

• Created:

  22/Jan/08 6:29 PM

  Updated:

  17/Dec/10 1:04 PM

  Resolved:

  04/Feb/08 11:11 AM