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

Performance Improvement for message_working table

    XMLWordPrintable

    Details

    • Testing Instructions:
      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".
    • Workaround:
      Hide

      n.a.

      Show
      n.a.
    • Affected Branches:
      MOODLE_22_STABLE, MOODLE_23_STABLE, MOODLE_24_STABLE, MOODLE_25_STABLE
    • Fixed Branches:
      MOODLE_23_STABLE, MOODLE_24_STABLE
    • Pull Master Branch:
      MDL-34933_master

      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

            Activity

              People

              Assignee:
              andyjdavis Andrew Davis
              Reporter:
              bushido Mark Nielsen
              Peer reviewer:
              Justin Filip
              Integrator:
              Damyon Wiese
              Tester:
              Dan Poltawski
              Participants:
              Component watchers:
              Jake Dallimore, Jun Pataleta
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                13/May/13