Slow search in 'Search people and messages'

Note: Test on different databases. At least PostgreSQL and MySQL

Before the patch

1. Check out a version of your site before the patch was applied.
2. Install the site
3. Create a few users (e.g. using MDK, "mdk run users")
4. Download and run the script to generate messages on your site: testmessage_gen.php.
5. Log in as an admin (the script would create a lot of messages for the admin user)
6. Open the messaging panel
7. Enter "e" on the search text field but do not execute the search yet.
8. Add the following entry to the "$CFG->dboptions" array in config.php:

   'logslow' => 0.5,

9. Open your favourite database management software (e.g. pgAdmin, MySQL workbench, etc.)
10. Open the database and query the mdl_log_queries table. e.g.

    SELECT id, sqltext, exectime, sqlparams FROM mdl_log_queries ORDER BY id DESC;

11. Back on the browser window with the messaging pane open, run the search.
12. Rerun the query for the mdl_log_queries table. Check the query run when searching messages.
    • On Postgres, it would look like

      SELECT '' || m.id || '_' || m.useridfrom || '_' || mcm.userid AS uniqueid, m.id, ...

    • On MySQL, it would look like

      SELECT CONCAT(m.id, '_', m.useridfrom, '_', mcm.userid) AS uniqueid, m.id, ...

      If there are no such records, try lowering the value of the 'logslow' option.

13. Repeat the search until the exectime values have stabilised. Take note of this stabilised exectime value.
14. Comment out the logslow DB option.
15. Log in as another user that did not get as many messages (e.g. s10 or m3, if you ran the MDK users script).
16. Open the messaging panel.
17. Uncomment the logslow DB option.
18. Repeat the same procedure. Taking note of the stabilised exectime before the patch.

After the patch

1. Check out the moodle version with the patch applied. Upgrade the site if necessary.
2. Log in as admin.
3. Repeat the message search and take note of the exectime values with the patch applied. (You may need to run the search several times until the exectime values stabilise).
4. Check the mdl_log_queries table.
5. Confirm that either one of the following happen:
   • The message search query is no longer being recorded because it's not considered a slow query.
   • Or the message search query is logged in the mdl_log_queries table, but the exectime is now better than before the patch.
6. Repeat the procedure above for the second user
7. Confirm that either one of the following happen:
   • The message search query is no longer being recorded because it's not considered a slow query.
   • Or the message search query is logged in the mdl_log_queries table, but the exectime is now better than before the patch.