MDL-37431 question preview: cron performance problem.
authorTim Hunt <T.J.Hunt@open.ac.uk>
Wed, 9 Jan 2013 15:35:10 +0000 (15:35 +0000)
committerTim Hunt <T.J.Hunt@open.ac.uk>
Fri, 11 Jan 2013 13:03:28 +0000 (13:03 +0000)
This query was performing badly. The solution seems to be to add a
redundant JOIN and WHERE to the subquery.

I renamed the table aliases in the subquery to avoid name collisions
elsewhere. I am afraid that makes the diff rather unhelpful. The
    AND subq_qu.component = :qubacomponent2
line is new. The other lines have just had subq_ added.

question/previewlib.php

index 063446e..7b7d85e 100644 (file)
@@ -341,14 +341,16 @@ function question_preview_cron() {
             'quba.component = :qubacomponent
                     AND NOT EXISTS (
                         SELECT 1
-                          FROM {question_attempts} qa
-                          JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
-                         WHERE qa.questionusageid = quba.id
-                           AND (qa.timemodified > :qamodifiedcutoff
-                                    OR qas.timecreated > :stepcreatedcutoff)
+                          FROM {question_attempts}      subq_qa
+                          JOIN {question_attempt_steps} subq_qas ON subq_qas.questionattemptid = subq_qa.id
+                          JOIN {question_usages}        subq_qu  ON subq_qu.id = subq_qa.questionusageid
+                         WHERE subq_qa.questionusageid = quba.id
+                           AND subq_qu.component = :qubacomponent2
+                           AND (subq_qa.timemodified > :qamodifiedcutoff
+                                    OR subq_qas.timecreated > :stepcreatedcutoff)
                     )
             ',
-            array('qubacomponent' => 'core_question_preview',
+            array('qubacomponent' => 'core_question_preview', 'qubacomponent2' => 'core_question_preview',
                 'qamodifiedcutoff' => $lastmodifiedcutoff, 'stepcreatedcutoff' => $lastmodifiedcutoff));
 
     question_engine::delete_questions_usage_by_activities($oldpreviews);