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

Performance problem in datbase activity module - missing index

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 1.9.8
    • Fix Version/s: None
    • Labels:
    • Environment:
      Apache + MySQL (MyISAM) running on server with 2 x Quad-Core Xeon, 4 GB RAM, Ubuntu Server edition 8.04 LTS
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_19_STABLE

      Description

      In a database activity with many entries (approx. 1200), performance was very low for e.g. the list view to be displayed. Pages needed up to 5 seconds to be generated, causing 100% load on one CPU core, on an otherwise idle server.

      The problem turned out to be caused by queries of the following type:

      SELECT COUNT(DISTINCT c.recordid) FROM mdl_data_content c,mdl_data_records r,mdl_data_content cs, mdl_user u WHERE c.recordid = r.id
      AND c.fieldid = 32
      AND r.dataid = 10
      AND r.userid = u.id
      AND cs.recordid = r.id

      More precisely, there is no usable index for the "cs.recordid = r.id" join, which leads to full table scans.

      Adding an index in the "mdl_data_content" table, on the "recordid" column, improved performance dramatically.

        Gliffy Diagrams

          Activity

          bostelm Henning Bostelmann created issue -
          dougiamas Martin Dougiamas made changes -
          Field Original Value New Value
          Workflow jira [ 36347 ] MDL Workflow [ 46231 ]
          dougiamas Martin Dougiamas made changes -
          Workflow MDL Workflow [ 46231 ] MDL Full Workflow [ 74534 ]
          bostelm Henning Bostelmann made changes -
          Labels performance
          bostelm Henning Bostelmann made changes -
          Link This issue has been marked as being related by MDL-17327 [ MDL-17327 ]
          poltawski Dan Poltawski made changes -
          Status Open [ 1 ] Closed [ 6 ]
          Resolution Duplicate [ 3 ]

            People

            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: