-
Improvement
-
Resolution: Fixed
-
Minor
-
3.2.5, 3.2.6, 3.3.2, 3.3.3, 3.4, 3.4.1
-
MOODLE_32_STABLE, MOODLE_33_STABLE, MOODLE_34_STABLE
-
MOODLE_35_STABLE
-
We're noticing several slow queries from quiz_overview_regrades joins on questionusageid in mod/quiz/report/overview/overview_table.php and mod/quiz/report/overview/report.php.
Currently we have about 30,000 entries in quiz_overview_regrades, causing long queries like this:
# Query_time: 5.461517 Lock_time: 0.000181 Rows_sent: 0 Rows_examined: 30645 Rows_affected: 0 |
# Bytes_sent: 50 |
SET timestamp=1520346177; |
SELECT 1 |
FROM mdl_user u
|
LEFT JOIN mdl_quiz_attempts quiza ON
|
quiza.userid = u.id AND quiza.quiz = '109764' |
JOIN mdl_user_enrolments ej1_ue ON ej1_ue.userid = u.id
|
JOIN mdl_enrol ej1_e ON (ej1_e.id = ej1_ue.enrolid AND ej1_e.courseid = '12345') |
JOIN mdl_quiz_overview_regrades qor ON qor.questionusageid = quiza.uniqueid
|
WHERE quiza.preview = 0 AND quiza.id IS NOT NULL AND 1 = 1 AND u.deleted = 0 LIMIT 0, 1; |
and more importantly
# Query_time: 10.740157 Lock_time: 0.000270 Rows_sent: 110 Rows_examined: 47391836 Rows_affected: 0 |
# Bytes_sent: 18691 |
SET timestamp=1520346187; |
SELECT
|
DISTINCT CONCAT(u.id, '#', COALESCE(quiza.attempt, 0)) AS uniqueid, |
(CASE WHEN (quiza.state = 'finished' AND NOT EXISTS ( |
SELECT 1 FROM mdl_quiz_attempts qa2 |
WHERE qa2.quiz = quiza.quiz AND
|
qa2.userid = quiza.userid AND
|
qa2.state = 'finished' AND ( |
COALESCE(qa2.sumgrades, 0) > COALESCE(quiza.sumgrades, 0) OR |
(COALESCE(qa2.sumgrades, 0) = COALESCE(quiza.sumgrades, 0) AND qa2.attempt < quiza.attempt) |
))) THEN 1 ELSE 0 END) AS gradedattempt, |
quiza.uniqueid AS usageid,
|
quiza.id AS attempt,
|
u.id AS userid,
|
u.idnumber, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname,
|
u.picture,
|
u.imagealt,
|
u.institution,
|
u.department,
|
u.email,
|
quiza.state,
|
quiza.sumgrades,
|
quiza.timefinish,
|
quiza.timestart,
|
CASE WHEN quiza.timefinish = 0 THEN null |
WHEN quiza.timefinish > quiza.timestart THEN quiza.timefinish - quiza.timestart
|
ELSE 0 END AS duration, COALESCE(( |
SELECT MAX(qqr.regraded)
|
FROM mdl_quiz_overview_regrades qqr
|
WHERE qqr.questionusageid = quiza.uniqueid
|
), -1) AS regraded |
FROM mdl_user u
|
LEFT JOIN mdl_quiz_attempts quiza ON
|
quiza.userid = u.id AND quiza.quiz = '109764' |
JOIN mdl_user_enrolments ej1_ue ON ej1_ue.userid = u.id
|
JOIN mdl_enrol ej1_e ON (ej1_e.id = ej1_ue.enrolid AND ej1_e.courseid = '12345') |
WHERE quiza.preview = 0 AND quiza.id IS NOT NULL AND 1 = 1 AND u.deleted = 0 |
ORDER BY quiza.id ASC LIMIT 0, 110; |
Adding an index on questionusageid fixes the problem. Here are the EXPLAIN statements before and after executing
ALTER TABLE mdl_quiz_overview_regrades ADD KEY mdl_quizoverregr_que_ix (questionusageid);
|
BEFORE:
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
1 | SIMPLE | ej1_e | NULL | ref | PRIMARY,mdl_enro_cou_ix | mdl_enro_cou_ix | 8 | const | 1 | 100.00 | Using where; Using index |
1 | SIMPLE | ej1_ue | NULL | ref | mdl_userenro_enruse_uix,mdl_userenro_enr_ix,mdl_userenro_use_ix | mdl_userenro_enruse_uix | 8 | moodle.ej1_e.id | 24 | 100.00 | Using index |
1 | SIMPLE | u | NULL | eq_ref | PRIMARY,mdl_user_del_ix | PRIMARY | 8 | moodle.ej1_ue.userid | 1 | 50.00 | Using where |
1 | SIMPLE | quiza | NULL | ref | PRIMARY,mdl_quizatte_uni_uix,mdl_quizatte_quiuseatt_uix,mdl_quizatte_qui_ix,mdl_quizatte_use_ix | mdl_quizatte_quiuseatt_uix | 16 | const,moodle.ej1_ue.userid | 1 | 9.00 | Using index condition; Using where |
1 | SIMPLE | qor | NULL | ALL | NULL | NULL | NULL | NULL | 26907 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
AFTER:
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
1 | SIMPLE | ej1_e | NULL | ref | PRIMARY,mdl_enro_cou_ix | mdl_enro_cou_ix | 8 | const | 1 | 100.00 | Using where; Using index |
1 | SIMPLE | ej1_ue | NULL | ref | mdl_userenro_enruse_uix,mdl_userenro_enr_ix,mdl_userenro_use_ix | mdl_userenro_enruse_uix | 8 | moodle.ej1_e.id | 24 | 100.00 | Using index |
1 | SIMPLE | u | NULL | eq_ref | PRIMARY,mdl_user_del_ix | PRIMARY | 8 | moodle.ej1_ue.userid | 1 | 50.00 | Using where |
1 | SIMPLE | quiza | NULL | ref | PRIMARY,mdl_quizatte_uni_uix,mdl_quizatte_quiuseatt_uix,mdl_quizatte_qui_ix,mdl_quizatte_use_ix | mdl_quizatte_quiuseatt_uix | 16 | const,moodle.ej1_ue.userid | 2 | 9.00 | Using index condition; Using where |
1 | SIMPLE | qor | NULL | ref | mdl_quizoverregr_que_ix | mdl_quizoverregr_que_ix | 8 | moodle.quiza.uniqueid | 1 | 100.00 | Using index |
- caused a regression
-
MDL-62698 MDL-61605 upgrade step can cause unique key violations
- Closed