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

Improve performance of the recently accessed courses query

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      This issue does not change any functionality, this is only a regression test

      1. Create three courses and a user
      2. Open guest access to course1 (go to course participants, then select enrolment methods from the edit cog, then click an "eye" next to guest enrolments)
      3. Enrol user into course2 using two enrolment methods
      4. login as this user
      5. Visit both course1 and course2
      6. Go to your dashboard and make sure that in the "Recent courses" block both courses are displayed
      7. As an admin who is not enrolled in any of these courses, visit all three courses
      8. Go to your dashboard
      9. You should see only course1 in the "recent courses" block
      Show
      This issue does not change any functionality, this is only a regression test Create three courses and a user Open guest access to course1 (go to course participants, then select enrolment methods from the edit cog, then click an "eye" next to guest enrolments) Enrol user into course2 using two enrolment methods login as this user Visit both course1 and course2 Go to your dashboard and make sure that in the "Recent courses" block both courses are displayed As an admin who is not enrolled in any of these courses, visit all three courses Go to your dashboard You should see only course1 in the "recent courses" block
    • Affected Branches:
      MOODLE_310_STABLE, MOODLE_39_STABLE, MOODLE_400_STABLE
    • Fixed Branches:
      MOODLE_310_STABLE, MOODLE_39_STABLE
    • Pull 3.9 Branch:
    • Pull 3.10 Branch:
      MDL-70023-310
    • Pull Master Branch:
      MDL-70023-master

      Description

      This has been initially reported in the WP project, I'll copy here the description. It especially affects workplace because we display user courses on every page in the user dropdown.

      Hi,
      One of our clients is experiencing significant page load times for one particular user. Our investigations have narrowed this down to course/lib.php course_get_recent_courses() method's SQL query. The query in question can take up to 17s to run for one particular user, when logged in as other users it executes in less than 3s.
      The query in question for the affected user returns 23 records, so not a huge number. There are no records for the user in the mdl_favourite table, and the slowness appears to emanate from the subquery in the WHERE clause.
      I have noticed that the fields being matched against do not have db indexes associated, which may improve performance.
      I have run the entire query on my local system against a db clone, and it took over 10 seconds to run for me. This seems like a very unreasonable query time for such a well used method.

      To further investigate this issue, ran the subquery directly with two simple modifications. I hardcoded the userid to the affected user's id, and removed the e.courseid = c.id clause. This was to test the speed of this subquery. The query returned 38 records and took 22 seconds.
      For your information, the mdl_enrol table contains 2437 records, and mdl_user_enrolments contains 3,105,608 records.
      I would appreciate if Moodle HQ could take a look at how best to improve the efficiency of this SQL.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marina Marina Glancy
              Reporter:
              marina Marina Glancy
              Integrator:
              Eloy Lafuente (stronk7)
              Tester:
              Bas Brands
              Participants:
              Component watchers:
              Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                18/Jan/21

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 40 minutes
                  1d 40m