Working on one single day of stats on our site (a pilot of only 30-odd courses but with 30000 mostly-unused rows in the user table), MySQL struggled for 6 HOURS on a single stats query!!
2 Hours after this particular query, stats was still processing with MySQL locked up at 100% usage and not allowing moodle to connect.
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 to look at from our little DB is insane.
# Time: 090306 6:30:42 # User@Host: moodle[moodle] @ localhost [] # 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);