We have a very big platform (350 000 users).
The users subscribe by email so $CFG->authloginviaemail is activated.
We see lots of slow queries :
Time: 200318 14:49:35
- Thread_id: 2003240 Schema: XXX_prod QC_hit: No
- Query_time: 1.043907 Lock_time: 0.000049 Rows_sent: 1 Rows_examined: 350536
- Rows_affected: 0 Bytes_sent: 4245
SELECT * FROM mdl_user WHERE LOWER(email) LIKE LOWER('xxxxxx') COLLATE utf8mb4_bin ESCAPE '|' AND mnethostid = '1' AND deleted=0 AND suspended=0 LIMIT 0, 1;
Examinating the problem, it appears that this is because of the lower (email).
The mdl_user table has an index on email but this index is not used because of the lower function.
I tested on various big platforms of ours and
select email from mdl_user where email <> lower(email)
request has no result.
It seems thus that email is lowered on user creation / modification.
I think it is then safe to remove the lower function in authenticate_user_login function of lb/moodlelib.php.