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

Slow messages query

    XMLWordPrintable

Details

    • MOODLE_28_STABLE
    • MOODLE_29_STABLE
    • MDL-48108-master
    • 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)
    • Team Beards Sprint 3
    • 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

              dobedobedoh Andrew Lyons
              mspurrier Matt Spurrier
              Ankit Agarwal Ankit Agarwal
              Dan Poltawski Dan Poltawski
              Damyon Wiese Damyon Wiese
              Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Laurent David, Raquel Ortega, Sara Arjona (@sarjona), Matteo Scaramuccia, Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                11/May/15