Details
Bug
Closed
Blocker
Description
A badly designed query slows the upgrade down to a crawl on a live site using quizzes:
SELECT u.id, u.id AS userid, g.grade AS rawgrade, g.timemodified AS dategraded, a.timefinish AS datesubmitted
FROM user u, quiz_grades g, quiz_attempts a
WHERE u.id = g.userid AND g.quiz = 8
AND a.timefinish = (SELECT MAX(aa.timefinish) FROM quiz_attempts aa WHERE aa.quiz = 8 AND aa.userid = u.id AND aa.preview = 0) ;
The correct way to get the desired results is:
SELECT u.id, u.id AS userid, g.grade AS rawgrade, g.timemodified AS dategraded, MAX(a.timefinish) AS datesubmitted
FROM user u, quiz_grades g, quiz_attempts a
WHERE u.id = g.userid AND g.quiz = 8 AND u.id = a.userid AND a.quiz = 8
GROUP BY u.id, g.grade, g.timemodified;
Which executes super-fast. Thanks Eloy for your analysis!
SQL fix applied and tested with the same test data. Upgrade completes without problems.