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

Performance Improvement for message_working table

XMLWordPrintable

    • 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".

      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)

            andyjdavis Andrew Davis
            bushido Mark Nielsen (Inactive)
            Justin Filip Justin Filip (Inactive)
            Damyon Wiese Damyon Wiese
            Dan Poltawski Dan Poltawski
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.