-
Improvement
-
Resolution: Deferred
-
Minor
-
None
-
2.6
-
MySQL
-
MOODLE_26_STABLE
There is a query that can become exceptionally slow on large datasets in Moodle 2.6.1+ /mod/scorm/report/graphs/graph.php (line 74-78)
We are seeing it take up to 38 seconds to return from the query with just under 1 million rows in `mdl_scorm_scoes_track`.
This is on a Solaris MySQL 5.5 server (can replicate on RHEL - MySQL 5.1).
This seems to be because the query does a DISTINCT on uniqueid which is non-indexable.
I seem to get the same result set by removing the DISTINCT and doing a GROUP BY on u.id and st.attempt instead. Though I did need to add an index to '`mdl_scorm_scoes_track`.attempt' first. This reduced query time to approx 0.021 seconds. Not sure what effect that has on graph though.
Current SQL:
SELECT DISTINCT CONCAT(u.id, '#', COALESCE(st.attempt, 0)) AS uniqueid, st.scormid AS scormid, st.attempt AS attempt, u.id AS userid,u.picture,u.firstname,u.lastname,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email,u.idnumber |
FROM mdl_user u |
LEFT JOIN mdl_scorm_scoes_track st |
ON st.userid = u.id AND st.scormid = ? |
WHERE u.id IN (?) AND (st.userid IS NOT NULL OR st.userid IS NULL) |
ORDER BY uniqueid |
LIMIT 0, 400
|
Proposed SQL:
SELECT CONCAT(u.id, '#', COALESCE(st.attempt, 0)) AS uniqueid, st.scormid AS scormid, st.attempt AS attempt, u.id AS userid,u.picture,u.firstname,u.lastname,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email,u.idnumber |
FROM mdl_user u |
LEFT JOIN mdl_scorm_scoes_track st |
ON st.userid = u.id AND st.scormid = ? |
WHERE u.id IN (?) AND (st.userid IS NOT NULL OR st.userid IS NULL) |
GROUP BY u.id,st.attempt |
ORDER BY uniqueid |
LIMIT 0, 400
|
- will be (partly) resolved by
-
-
- Closed
-