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

Performance Improvement for message_working table

    XMLWordPrintable

Details

    • MOODLE_22_STABLE, MOODLE_23_STABLE, MOODLE_24_STABLE, MOODLE_25_STABLE
    • MOODLE_23_STABLE, MOODLE_24_STABLE
    • MDL-34933_master
    • Hide

      n.a.

      Show
      n.a.
    • Hide

      Testing is really to verify that the index gets created as necessary.

      Moodle upgrade:

      1. On a site that is a version prior to the version in the branch being tested, verify that no index exists on the "message_working" table for the field "unreadmessageid".
      2. Install a codebase containing this update on that site.
      3. Run the Moodle update.
      4. Verify that the index is created on the table "message_working" for the field "unreadmessageid".

      Moodle install:

      1. Run a new Moodle install using a codebase containing this code.
      2. Verify that an index was created in the "message_working" table using the field "unreadmessageid".
      Show
      Testing is really to verify that the index gets created as necessary. Moodle upgrade: On a site that is a version prior to the version in the branch being tested, verify that no index exists on the "message_working" table for the field "unreadmessageid". Install a codebase containing this update on that site. Run the Moodle update. Verify that the index is created on the table "message_working" for the field "unreadmessageid". Moodle install: Run a new Moodle install using a codebase containing this code. Verify that an index was created in the "message_working" table using the field "unreadmessageid".

    Description

      Suggesting adding an index to the message_working.unreadmessageid field. When the tables start to grow, this query can take some time without the index. For example, for one site, it takes 1,891 ms to run the query.

      Here is the query:

      SELECT m.id, m.smallmessage, m.fullmessageformat, m.notification, u.firstname, u.lastname
      		 
      FROM mdl_message m
      		 
      JOIN mdl_message_working mw ON m.id=mw.unreadmessageid
      		 
      JOIN mdl_message_processors p ON mw.processorid=p.id
      		 
      JOIN mdl_user u ON m.useridfrom=u.id
      		 
      WHERE m.useridto = '?' AND p.name='?'

      Here is the trace:

      ...uery called at lib/dml/mysqli_native_moodle_database.php (808)
      		 
      ...le_database::get_records_sql called at lib/moodlelib.php (9911)
      		 
              in message_popup_window called at lib/pagelib.php (1230)
      		 
      in moodle_page::starting_output called at lib/pagelib.php (720)
      		 
      ...moodle_page::set_state called at lib/outputrenderers.php (632)
      		 
          in core_renderer::header called at mod/forum/view.php (113)

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-35302 Missing index on message_working

          • Major - Major loss of function, incorrect output
          • Closed
          will help resolve

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-38536 message popup window can get very slow when number of messages is large.

          • Major - Major loss of function, incorrect output
          • Closed

          Activity

            People

              andyjdavis Andrew Davis
              bushido Mark Nielsen
              Justin Filip Justin Filip
              Damyon Wiese Damyon Wiese
              Dan Poltawski Dan Poltawski
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                13/May/13