MDL-37250 mod_lesson: Move conditions to queries for better performance

While this ony seems to affect MySQL, better put the filter/conditions
on every query so the temporary view/table does not need to handle
thousands of potentially unrelated records.

diff --git a/mod/lesson/report.php b/mod/lesson/report.php
index 9273e55..a1c4777 100644 (file)
--- a/mod/lesson/report.php
+++ b/mod/lesson/report.php
@@ -128,19 +128,23 @@ if ($action === 'delete') {
         list($esql, $params) = get_enrolled_sql($context, '', $currentgroup, true);
         list($sort, $sortparams) = users_order_by_sql('u');
 
-        $params['lessonid'] = $lesson->id;
+        $params['a1lessonid'] = $lesson->id;
+        $params['b1lessonid'] = $lesson->id;
+        $params['c1lessonid'] = $lesson->id;
         $ufields = user_picture::fields('u');
         $sql = "SELECT DISTINCT $ufields
                 FROM {user} u
                 JOIN (
                     SELECT userid, lessonid FROM {lesson_attempts} a1
+                    WHERE a1.lessonid = :a1lessonid
                         UNION
                     SELECT userid, lessonid FROM {lesson_branch} b1
+                    WHERE b1.lessonid = :b1lessonid
                         UNION
                     SELECT userid, lessonid FROM {lesson_timer} c1
+                    WHERE c1.lessonid = :c1lessonid
                     ) a ON u.id = a.userid
                 JOIN ($esql) ue ON ue.id = a.userid
-                WHERE a.lessonid = :lessonid
                 ORDER BY $sort";
 
         $students = $DB->get_recordset_sql($sql, $params);