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

Improve message_get_recent_conversations query performance

    XMLWordPrintable

    Details

    • Database:
      MySQL
    • Affected Branches:
      MOODLE_29_STABLE

      Description

      The query from function message_get_recent_conversations performs poorly on MySQL 5.5.44.

          $sql = "SELECT $userfields,
                         message.id as mid, message.notification, message.smallmessage, message.fullmessage,
                         message.fullmessagehtml, message.fullmessageformat, message.timecreated,
                         contact.id as contactlistid, contact.blocked
       
                    FROM {message_read} message
                    JOIN {user} otheruser ON otheruser.id = CASE
                                      WHEN message.useridto = :userid1 THEN message.useridfrom
                                                                       ELSE message.useridto END
               LEFT JOIN {message_contacts} contact ON contact.userid = :userid2 AND contact.contactid = otheruser.id
       
                   WHERE otheruser.deleted = 0
                     AND (message.useridto = :userid3 OR message.useridfrom = :userid4)
                     AND message.notification = 0
                     AND NOT EXISTS (
                              SELECT 1
                                FROM {message_read} othermessage
                               WHERE ((othermessage.useridto = :userid5 AND othermessage.useridfrom = otheruser.id) OR
                                      (othermessage.useridfrom = :userid6 AND othermessage.useridto = otheruser.id))
                                 AND (othermessage.timecreated > message.timecreated OR (
                                      othermessage.timecreated = message.timecreated AND othermessage.id > message.id))
                         )
       
                ORDER BY message.timecreated DESC";
      

      It takes less than 2 seconds when run on my mdl_message table with 47375 rows but it takes over 10 seconds when run on mdl_message_read with 85129 rows.

      Entry from slow log - notice "Rows_examined: 4183610":

      # Query_time: 11.531431  Lock_time: 0.000210 Rows_sent: 100  Rows_examined: 4183610
      SELECT otheruser.id,otheruser.picture,otheruser.firstname,otheruser.lastname,otheruser.firstnamephonetic,otheruser.lastnamephonetic,otheruser.middlename,otheruser.alternatename,otheruser.imagealt,otheruser.email,otheruser.lastaccess,
                         message.id as mid, message.notification, message.smallmessage, message.fullmessage,
                         message.fullmessagehtml, message.fullmessageformat, message.timecreated,
                         contact.id as contactlistid, contact.blocked
       
                    FROM mdl_message_read message
                    JOIN mdl_user otheruser ON otheruser.id = CASE
                                      WHEN message.useridto = '72' THEN message.useridfrom
                                                                       ELSE message.useridto END
               LEFT JOIN mdl_message_contacts contact ON contact.userid = '72' AND contact.contactid = otheruser.id
       
                   WHERE otheruser.deleted = 0
                     AND (message.useridto = '72' OR message.useridfrom = '72')
                     AND message.notification = 0
                     AND NOT EXISTS (
                              SELECT 1
                                FROM mdl_message_read othermessage
                               WHERE ((othermessage.useridto = '72' AND othermessage.useridfrom = otheruser.id) OR
                                      (othermessage.useridfrom = '72' AND othermessage.useridto = otheruser.id))
                                 AND (othermessage.timecreated > message.timecreated OR (
                                      othermessage.timecreated = message.timecreated AND othermessage.id > message.id))
                         )
       
                ORDER BY message.timecreated DESC LIMIT 0, 100;
      

      My mdl_user table has 27159 rows.
      mdl_message_contacts 2015 rows.

      The performance greatly depends on the # of rows matched, e.g.;
      100 total, Query took 11.6246 seconds
      748 total, Query took 136.8964 seconds

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              dobedobedoh Andrew Lyons
              Reporter:
              tmuras Tomasz Muras
              Participants:
              Component watchers:
              Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Sara Arjona (@sarjona), Matteo Scaramuccia, Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: