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

Slow query on login when $CFG-authloginviaemail is activated

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Minor
    • Resolution: Duplicate
    • 3.8.2
    • None
    • Authentication
    • None
    • MOODLE_38_STABLE

    Description

      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.

       

      Attachments

        1. patch
          5 kB
        2. patch.diff
          5 kB
        3. sqpatch
          6 kB

        Issue Links

          Activity

            People

              Unassigned Unassigned
              ndieschburg Noel Dieschburg
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: