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

Improve performance of function message_search_users

    XMLWordPrintable

Details

    Description

      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();

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              toga Tobias Garske
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated: