Hardware and performance

Hi Matt

Category level enrollments can be calculated in a UNION just like it's done now [1]. I think they will perform better.

On the other hand. Query still is really slow. More can be done: create a temporary table with the day logs and index it correctly. For instance:

CREATE TABLE mdl_tmp_stats_logs
SELECT *
FROM mdl_log l
WHERE l.time >= 1236164400 AND l.time < 1236250800;

CREATE INDEX mdl_tmp_stats_logs_time_idx ON mdl_tmp_stats_logs ( time );
CREATE INDEX mdl_tmp_stats_logs_course_idx ON mdl_tmp_stats_logs ( course );

(
and all the indexes necessary... to simplify the creation you can use this trick:

CREATE TABLE mdl_tmp_stats_logs LIKE mdl_log;
INSERT INTO mdl_tmp_stats_logs select * from mdl_log l WHERE l.time >= 1236164400 AND l.time < 1236250800;
)


Now rewrite the query as:

UPDATE mdl_stats_daily
 SET stat2 = (
 SELECT COUNT(DISTINCT e.userid)
 FROM stats_enrolments e
 INNER JOIN mdl_tmp_stats_logs l ON l.userid = e.userid AND l.course = e.courseid
 WHERE e.courseid = mdl_stats_daily.courseid)
 WHERE mdl_stats_daily.stattype = 'enrolments' AND
 mdl_stats_daily.timeend = 1236250800 AND
 mdl_stats_daily.roleid = 0 AND
 mdl_stats_daily.courseid IN
 (SELECT DISTINCT course
 FROM mdl_tmp_stats_logs);

This performs much, much better (less than a second... if i'm not wrong... let's see the others ;) )

Now you'll have to calculate a mdl_log "view" for each day... but it can be reused (as stats_enrolments) in the other queries.

On the other hand, using TEMPORARY table can produce problems in a replication configuration. Maybe removing the word TEMPORARY and CREATING and DROPING the table in code should be much better.

Hope this helps ;)

---------
[1] The new stats_enrolments calculus for the example ( just one category level):

CREATE TEMPORARY TABLE stats_enrolments
SELECT DISTINCT ra.roleid, ra.userid, c.id AS courseid
FROM mdl_role_assignments ra
INNER JOIN mdl_role_capabilities rc ON ra.roleid = rc.roleid AND rc.capability = 'moodle/course:view' AND rc.permission = 1 AND rc.contextid = 1
INNER JOIN mdl_context cx ON ra.contextid = cx.id AND cx.contextlevel = 10
CROSS JOIN mdl_course c
UNION
SELECT DISTINCT ra.roleid, ra.userid, c.id as courseid
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 (
(rc.capability = 'moodle/course:view' ) AND
rc.permission = 1 AND
rc.contextid = 1 AND
((cc1.id = ctx.instanceid AND ctx.contextlevel = 40)))
UNION
SELECT DISTINCT ra.roleid, ra.userid, c.id AS courseid
FROM mdl_role_assignments ra
INNER JOIN mdl_role_capabilities rc ON ra.roleid = rc.roleid AND rc.capability = 'moodle/course:view' AND rc.permission = 1 AND rc.contextid = 1
INNER JOIN mdl_context cx ON ra.contextid = cx.id AND cx.contextlevel = 50
INNER JOIN mdl_course c ON cx.instanceid = c.id

;
CREATE INDEX stats_enrol_idx ON stats_enrolments (userid, courseid);

I took a sql dump over to another server since I didn't want to test on the live server. The weird thing is that the *original* query then only took 6 seconds! Something funny going on there.

Juan's method is a bit quicker than Matts (.09 secs vs .76 secs), better than 6 secs but I haven't tested them on the problem server (which is working fine in all other respects).

INNODB might work better with regard to locking but the 1.2 billion rows was the primary cause.

-Bruce.

Hi Juan,

I don't disagree! - It would be good to get some feedback regarding the query Matt posted above, but that will take time/whereas Innodb might be a partial quick win for performance.