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

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

    XMLWordPrintable

Details

    • 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  

    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

          Activity

            People

              andrewmadden Andrew Madden
              andrewmadden Andrew Madden
              John Yao John Yao
              Jun Pataleta Jun Pataleta
              CiBoT CiBoT
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo, David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo, Matteo Scaramuccia, David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                9/Sep/19

                Time Tracking

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