-
Bug
-
Resolution: Duplicate
-
Minor
-
None
-
3.7.4, 3.8.1, 3.9
-
MOODLE_37_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
On big sites it takes 30-60 secs to open Task logs page even when log retention is enabled. It might take more time if `task_logretention` and `task_logretainruns` are set to bigger values than defaults.
It happens because this SQL uses LIKEs in WHERE clause and that blocks using db indexes.
If we can exclude logs that have classname in output field and replace LIKE by `=` for classname field we will let this SQL hit the index and drop down execution time to couple of seconds even on big sites.
Current SQL:
SELECT tl.id, tl.type, tl.component, tl.classname, tl.userid, tl.timestart, tl.timeend,
|
tl.dbreads, tl.dbwrites, tl.result,
|
tl.dbreads + tl.dbwrites AS db,
|
tl.timeend - tl.timestart AS duration,
|
u.id AS userid2,u.picture AS userpicture,u.firstname AS userfirstname,u.lastname AS userlastname,u.firstnamephonetic AS userfirstnamephonetic,u.lastnamephonetic AS userlastnamephonetic,u.middlename AS usermiddlename,u.alternatename AS useralternatename,u.imagealt AS userimagealt,u.email AS useremail
|
FROM mdl_task_log tl
|
LEFT JOIN mdl_user u ON u.id = tl.userid
|
WHERE (classname ILIKE '%core\\task\\task\_log\_cleanup\_task%' ESCAPE '\' OR output ILIKE '%core\\task\\task\_log\_cleanup\_task%' ESCAPE '\') |
ORDER BY timestart DESC;
|
Proposed SQL:
SELECT tl.id, tl.type, tl.component, tl.classname, tl.userid, tl.timestart, tl.timeend,
|
tl.dbreads, tl.dbwrites, tl.result,
|
tl.dbreads + tl.dbwrites AS db,
|
tl.timeend - tl.timestart AS duration,
|
u.id AS userid2,u.picture AS userpicture,u.firstname AS userfirstname,u.lastname AS userlastname,u.firstnamephonetic AS userfirstnamephonetic,u.lastnamephonetic AS userlastnamephonetic,u.middlename AS usermiddlename,u.alternatename AS useralternatename,u.imagealt AS userimagealt,u.email AS useremail
|
FROM mdl_task_log tl
|
LEFT JOIN mdl_user u ON u.id = tl.userid
|
WHERE classname = 'core\task\task_log_cleanup_task' |
ORDER BY timestart DESC;
|