Details
-
Bug
-
Status: Closed
-
Minor
-
Resolution: Fixed
-
3.5.6, 3.6.4, 3.7, 3.8
-
Any
-
MOODLE_35_STABLE, MOODLE_36_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE
-
MOODLE_35_STABLE, MOODLE_36_STABLE, MOODLE_37_STABLE
-
MDL-65926-master_lowercase_username_sql_query -
Description
It was discovered that the following SQL query it quite slow on large sites due to an inefficient index.
SELECT * FROM mdl_user WHERE LOWER(username) = LOWER($1) AND deleted <> 1 AND mnethostid = $2 |
By creating the following index the execution time, was decreased from 430ms to 1.4ms.
create index mdl_user_lower on mdl_user(lower(username));
|
The issue was discovered using postgres tools.
The source of the query is believed to be get_complete_user_data in lib/moodlelib.php which is used about 35 times in the core project.
The original proposal was to add a lowercase(username) index in the user table in order to improve the query time on large-scale sites.
Assumptions
There is an assumption that all usernames in the user table must be lowercase.
Update
As Moodle does not yet support functional indexes, it is proposed to make the query case sensitive to remove the need to use the LOWER functions in the query.
There is an assumption the username field will always contain a lowercase string in the mdl_user table. It follows the username input value can be forced to be lowercase in PHP, and then use a case sensitive SQL query to retrieve the user data. This allows a case insensitive input, while allowing for an efficient case sensitive SQL query.
Edit: See comment for analysis of enforcement of lowercase username for user.
Attachments
Issue Links
- is a regression caused by
-
MDL-64950 Username is being handled in a case-sensitive manner on the forgot password page
-
- Closed
-