Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-67744

Optimize SQL for Task logs page

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Duplicate
    • Icon: Minor Minor
    • None
    • 3.7.4, 3.8.1, 3.9
    • Tasks
    • 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;
      

            mikhailgolenkov Misha Golenkov
            mikhailgolenkov Misha Golenkov
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.