On 1.9.3+ (Build: 20090109) stats are completely unusable for us.

My server tried to runs stats for ONE SINGLE DAY of logs on our site (a pilot of only 30-odd courses but with 30000 mostly-unused rows in the user table). After 8 HOURS, I had to restart MySQL in the morning as it was still locked up at 100% and moodle could not connect to the DB.

MySQL struggled for 6 HOURS on a single query!!

Here is the monstrous query. We showed it to our Computer Science SQL lecturer for a laugh who said 'it is definitely a very bad query. Using cross join on large tables is a definite NO'. Creating 1.2 billion rows from our little DB is insane.

1. Time: 090306 6:30:42
2. User@Host: moodle[moodle] @ localhost []
3. Query_time: 21631 Lock_time: 0 Rows_sent: 0 Rows_examined: 1199857693
   UPDATE mdl_stats_daily
   SET stat2 = (SELECT COUNT(DISTINCT ra.userid)
   FROM mdl_role_assignments ra JOIN mdl_context ctx
   ON ctx.id = ra.contextid
   CROSS JOIN mdl_course c
   JOIN mdl_role_capabilities rc
   ON rc.roleid = ra.roleid LEFT OUTER JOIN mdl_course_categories cc1
   ON cc1.id = c.category
   WHERE ra.roleid = mdl_stats_daily.roleid AND
   c.id = mdl_stats_daily.courseid AND
   ((rc.capability = 'moodle/course:view' )
   AND rc.permission = 1 AND rc.contextid = 1
   AND (ctx.contextlevel = 10
   OR (c.id = ctx.instanceid AND ctx.contextlevel = 50) OR (cc1.id = ctx.instanceid AND ctx.contextlevel = 40))) AND
   EXISTS (SELECT 'x'
   FROM mdl_log l
   WHERE l.course = mdl_stats_daily.courseid AND
   l.userid = ra.userid AND l.time >= 1236164400 AND l.time < 1236250800))
   WHERE mdl_stats_daily.stattype = 'enrolments' AND
   mdl_stats_daily.timeend = 1236250800 AND
   mdl_stats_daily.courseid IN
   (SELECT DISTINCT l.course
   FROM mdl_log l
   WHERE l.time >= 1236164400 AND l.time < 1236250800);