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.