From 016a65157884e37248dc439f40f3d5904fdbda4d Mon Sep 17 00:00:00 2001 From: Tim Hunt Date: Wed, 9 Jan 2013 15:35:10 +0000 Subject: [PATCH] MDL-37431 question preview: cron performance problem. 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 | 14 ++++++++------ 1 file changed, 8 insertions(+), 6 deletions(-) diff --git a/question/previewlib.php b/question/previewlib.php index 4f8970bafbf..fc03097260f 100644 --- a/question/previewlib.php +++ b/question/previewlib.php @@ -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); -- 2.36.1