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

Use case sensitive query for username in user table to decrease execution time.

XMLWordPrintable

    • 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
    • Hide

      Setup

      1. Create a Moodle application with a PostgreSQL database.
      2. Login as an admin.
      3. GOTO Site administration->Development tab.
      4. Click on 'Make test course'.
      5. Size of the course should be Large or Extra Large. This can take some time.
      6. Fill in the rest of the required fields with dummy data.
      7. Click 'Create course'

      Test the efficiency of new SQL query

      1. Login to the PostgreSQL database.
      2. Execute 

        EXPLAIN ANALYZE SELECT * FROM mdl_user WHERE LOWER(username) = LOWER('john') AND deleted <> 1 AND mnethostid = 1;

      3. Execute 

        EXPLAIN ANALYZE SELECT * FROM mdl_user WHERE username = 'john' AND deleted <> 1 AND mnethostid = 1;

      4. Confirm that the first query uses a Seq Scan.
      5. Confirm that the second query uses an Index Scan.
      6. Confirm that the second query takes less execution time than the first
         

      Test that code now executes desired query

      1. Ensure that logging is enabled in your database. Instructions for psql.
      2. Open moodle application in a web browser
      3. If already logged in, log out.
      4. Enter login information and click login. Can be any type of user.
      5. Open psql log.
      6. Use a search function such as `/` in vim and search for the statement below.
      7. Verify that the following statement was found in the search. 

        SELECT * FROM mdl_user WHERE username = $1 AND deleted <> 1 AND mnethostid = $2

       Test altered code does not affect existing business logic

      1. Run existing unit tests in Moodle
      2. Tests should be run via Travis CI using the moodle core travis.yml config file.
      3. Ensure that all unit tests still pass

       

      Show
      Setup Create a Moodle application with a PostgreSQL database. Login as an admin. GOTO Site administration->Development tab. Click on 'Make test course'. Size of the course should be Large or Extra Large. This can take some time. Fill in the rest of the required fields with dummy data. Click 'Create course' Test the efficiency of new SQL query Login to the PostgreSQL database. Execute EXPLAIN ANALYZE SELECT * FROM mdl_user WHERE LOWER (username) = LOWER ( 'john' ) AND deleted <> 1 AND mnethostid = 1; Execute EXPLAIN ANALYZE SELECT * FROM mdl_user WHERE username = 'john' AND deleted <> 1 AND mnethostid = 1; Confirm that the first query uses a Seq Scan. Confirm that the second query uses an Index Scan . Confirm that the second query takes less execution time than the first   Test that code now executes desired query Ensure that logging is enabled in your database. Instructions for psql. Open moodle application in a web browser If already logged in, log out. Enter login information and click login. Can be any type of user. Open psql log. Use a search function such as `/` in vim and search for the statement below. Verify that the following statement was found in the search. SELECT * FROM mdl_user WHERE username = $1 AND deleted <> 1 AND mnethostid = $2  Test altered code does not affect existing business logic Run existing unit tests in Moodle Tests should be run via Travis CI using the moodle core travis.yml config file. Ensure that all unit tests still pass  

      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.

       

            andrewmadden Andrew Madden
            andrewmadden Andrew Madden
            John Yao John Yao
            Jun Pataleta Jun Pataleta
            CiBoT CiBoT
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 3 hours, 1 minute
                3h 1m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.