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

Moodle messaging function optimisation





      Case: We have a client that makes extensive use of the Moodle messages function, this leads to hundreds of messages in the messages drawer. When teachers on the site log in the drawer locks in a PHP slot for such a long time that the user will experience timeouts and errors and the sites becomes unusable. See attached the DB error screenshots

      Our developers came up with a solution to this and we managed to bring loading time down from over 530 sec to less than 1 sec.

      The message query in the /message/classes/api.php file in the get_conversation_counts static function has been updated.

      We noted that the standard Moodle query had a sub-select which was rewritten to rather use joins. It increased the SQL verbosity, but it runs way faster now (533 seconds reduced to less than 1 second). The modified SQL starts on line 1614. The suggested code improvement is provided (attached), and we split the subselect into three UNIONed queries for the three cases that the system needs to query on.

      This change however caused the messages function to produce errors on mobile and the website. We need Moodle HQ to optimise the site code or make use of our code and use it to fix the messages function code.


        1. api.php
          150 kB
        2. browser screenshot.jpg
          browser screenshot.jpg
          83 kB
        3. db connection failed.png
          db connection failed.png
          231 kB
        4. patch.txt
          4 kB
        5. Wingu Moodle app error.jpg
          Wingu Moodle app error.jpg
          185 kB



            Unassigned Unassigned
            giovannivosloo Giovanni Vosloo
            4 Vote for this issue
            4 Start watching this issue




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