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

Performance Improvement for message_working table

    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)

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Votes:
                  0 Vote for this issue
                  Watchers:
                  8 Start watching this issue

                  Dates

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