From 726051dcf945a1bf80525d86308b6f44d76555fc Mon Sep 17 00:00:00 2001 From: "Eloy Lafuente (stronk7)" Date: Tue, 12 Jul 2016 13:26:46 +0200 Subject: [PATCH] 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. --- mod/lesson/report.php | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) diff --git a/mod/lesson/report.php b/mod/lesson/report.php index 9273e5569da..a1c47775dae 100644 --- 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); -- 2.11.4.GIT