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

get_potential_users() function is slow on large site(s)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.1
    • Fix Version/s: None
    • Component/s: Enrolments
    • Labels:
    • Affected Branches:
      MOODLE_31_STABLE

      Description

      get_potential_users in enrol/locallib.php can take order of 10 to 20 seconds or more to execute. The SQL queries don't seem that optimal. For example, our site has around 80,000 users and a similar number of entries in the user_enrolments table. And...

      SELECT COUNT(1) FROM mdl_user u
                  LEFT JOIN mdl_user_enrolments ue ON (ue.userid = u.id AND ue.enrolid = '20460')
                      WHERE u.id <> '1' AND u.deleted = 0 AND u.confirmed = 1
                            AND ue.id IS NULL;
      

      takes around 15 seconds to complete. The corresponding explain looks like

      +----+-------------+-------+-------------+-----------------------------------------------------------------+---------------------------------+---------+--------------------+-------+----------------------------------------------------------------------------+
      | id | select_type | table | type        | possible_keys                                                   | key                             | key_len | ref                | rows  | Extra                                                                      |
      +----+-------------+-------+-------------+-----------------------------------------------------------------+---------------------------------+---------+--------------------+-------+----------------------------------------------------------------------------+
      |  1 | SIMPLE      | u     | index_merge | PRIMARY,mdl_user_del_ix,mdl_user_con_ix                         | mdl_user_del_ix,mdl_user_con_ix | 1,1     | NULL               | 21298 | Using intersect(mdl_user_del_ix,mdl_user_con_ix); Using where; Using index |
      |  1 | SIMPLE      | ue    | eq_ref      | mdl_userenro_enruse_uix,mdl_userenro_enr_ix,mdl_userenro_use_ix | mdl_userenro_enruse_uix         | 16      | const,moodle2.u.id |     1 | Using where; Using index; Not exists                                       |
      +----+-------------+-------+-------------+-----------------------------------------------------------------+---------------------------------+---------+--------------------+-------+----------------------------------------------------------------------------+
      2 rows in set (0.01 sec)
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              howardsmiller Howard Miller
              Participants:
              Component watchers:
              Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Víctor Déniz Falcón
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: