-
Bug
-
Resolution: Won't Do
-
Minor
-
None
-
3.1
-
MOODLE_31_STABLE
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)
|