We run a very large moodle instance with about 14 million activities in about 400,000 courses. More than 50,000 teachers manage their courses independently.

We noticed a query in message_search_user and improved its performance by splitting it into two parts, and so getting rid of a table join.

message/classes/api.php:243
$sql = "SELECT u.id
            FROM {user} u
            JOIN {message_contacts} mc
            ON (u.id = mc.contactid AND mc.userid = :userid1) OR (u.id = mc.userid AND mc.contactid = :userid2)
            WHERE u.deleted = 0
            AND u.confirmed = 1
            AND " . $DB->sql_like($fullname, ':search', false) . "
            AND u.id $exclude
        ORDER BY " . $DB->sql_fullname();

Was basically split into the following:

1.
$select = " (userid = :userid1) OR (contactid = :userid2) ";
$contacts = $DB->get_records_select('message_contacts', $select, $params, '', 'id, userid, contactid');

2.
$sql = "SELECT u.id
            FROM {user} u
            WHERE u.deleted = 0
            AND u.confirmed = 1
            AND " . $DB->sql_like($fullname, ':search', false) . "
            AND u.id $inuserids
        ORDER BY " . $DB->sql_fullname();