We're updating the issue view to help you get more done. 

Performance of user find query degraded in migrating to Devise

Description

SELECT `users`.* FROM `users` WHERE (lower(login) = ? OR lower(email) = ?) ORDER BY `users`.`id` ASC LIMIT ?

This table was retrieved with an index scan, which is like a full table scan, except it only hits the index (not the actual table). This is usually not great for performance.

The table was retrieved with this index: PRIMARY

You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.

Approximately 1 row of this table was scanned.

We logged a ticket with our MySQL support people and they gave a complex answer but it basically came down to the lowercase. We put this in ourselves but the MySQL comparison is case insensitive so we don't need to.

2.3.4 :007 > User.select("id, lower(email)").group(:email).having("count(id) > 1").first
=> nil
2.3.4 :008 > User.select("id, lower(email)").group(:email).having("count(id) > 1").size
=> {}
2.3.4 :009 > User.select("id, lower(login)").group(:email).having("count(id) > 1").size
=> {}
2.3.4 :010 > User.select("id, lower(login)").group(:email).having("count(id) > 1").first
=> nil

2.3.4 :018 > User.select("id,login").where(login: "zz9pzza").size
=> 1
2.3.4 :019 > User.select("id,login").where(login: "zz9Pzza").size
=> 1

Red found the fix and I concur

https://github.com/otwcode/otwarchive/blob/92fd56478d959cd6d3f934d3935e3eacdcb498e0/app/models/user.rb#L253-L254 (edited)

we remove `lower()` and downcase from there

Testing

Make sure we can log in with any case version of email or login

Environment

None

Status

Assignee

james_

Reporter

james_

Roadmap

Users

Priority

Medium

Affects versions

0.9.230

Fix versions

Components

BackEnd

Difficulty

Medium

Required Access Level

None

Milestone

Internal 0.9