Hello,

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

1. Thread_id: 2003240 Schema: XXX_prod QC_hit: No
2. Query_time: 1.043907 Lock_time: 0.000049 Rows_sent: 1 Rows_examined: 350536
3. Rows_affected: 0 Bytes_sent: 4245
   SET timestamp=1584539375;
   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.