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

Slow messages query

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide
      1. Run the messagelib phpunit tests
      2. Generate lots of messages between a number of users. I used the script that Ankit wrote initially, but have also modified it for my own purposes (attached). I left it running for a long time. For reference, I have over 25,000 messages between the main pair of users.
      3. Login as the user with lots and lots and lots of messages
      4. Open "Messages" from the user menu
      5. Select "Recent conversations" from the dropdown
        • Confirm that the page loads in a reasonable amount of time
      6. Prove that the patch is making a difference, revert it:
      7. Refresh the page
      8. Wait for an eternity (I gave up after 20 minutes)
      Show
      Run the messagelib phpunit tests Generate lots of messages between a number of users. I used the script that Ankit wrote initially, but have also modified it for my own purposes (attached). I left it running for a long time. For reference, I have over 25,000 messages between the main pair of users. Login as the user with lots and lots and lots of messages Open "Messages" from the user menu Select "Recent conversations" from the dropdown Confirm that the page loads in a reasonable amount of time Prove that the patch is making a difference, revert it: Refresh the page Wait for an eternity (I gave up after 20 minutes)
    • Affected Branches:
      MOODLE_28_STABLE
    • Fixed Branches:
      MOODLE_29_STABLE
    • Pull Master Branch:
      MDL-48108-master
    • Sprint:
      Team Beards Sprint 3
    • Issue size:
      Medium

      Description

      While looking through the slow query log, found this nice slow query:

      # Query_time: 14.251499  Lock_time: 0.000126 Rows_sent: 6  Rows_examined: 124720
      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 message message
                    JOIN user otheruser ON otheruser.id = CASE
                                      WHEN message.useridto = '1031471' THEN message.useridfrom
                                                                       ELSE message.useridto END
               LEFT JOIN message_contacts contact ON contact.userid = '1031471' AND contact.contactid = otheruser.id
       
                   WHERE otheruser.deleted = 0
                     AND (message.useridto = '1031471' OR message.useridfrom = '1031471')
                     AND message.notification = 0
                     AND NOT EXISTS (
                              SELECT 1
                                FROM message othermessage
                               WHERE ((othermessage.useridto = '1031471' AND othermessage.useridfrom = otheruser.id) OR
                                      (othermessage.useridfrom = '1031471' 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;
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              dobedobedoh Andrew Nicols
              Reporter:
              mspurrier Matt Spurrier
              Peer reviewer:
              Ankit Agarwal
              Integrator:
              Dan Poltawski
              Tester:
              Damyon Wiese
              Participants:
              Component watchers:
              Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              10 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                11/May/15