Finding users by e-mail address via user_load is slow

The import module finds existing users to update using the following code in user_import.module:

$existing_account = user_load(array('mail' => $fields['user']['email'][0]));

This creates the following SQL queries:

SELECT * FROM users u WHERE LOWER( mail ) = LOWER(  'user@host.com' )

There is an index on the mail column, but it is not used because of the LOWER() function. This makes this query, which is executed for every row, very slow. For my server and table size, it takes 0.3 seconds, while the query without the LOWER() calls runs in less than a millisecond. I personally think that most users run MySQL with a case insensitive column, so for the majority, the query is unnecessarily slow.

This issue is also raised as a Drupal issue, see #83738: LOWER(name) queries perform badly; add column name_lower and index.. However, it does not seem like it will be fixed soon, and even when it's fixed, it is most likely only fixed for the name column, not the mail column.

Because the user_import module processes a large number of users, it may be worth the effort to implement a separate user lookup query function, one that does the mail -> uid search not using the LOWER(). Preferably, it would check if MySQL is used and if the column collation is case-insensitive. If that is too much work, the option may be left to the administrator to match without LOWER(). Using the found uid, user_load() can be called to build the account object.

I will attach a patch that significantly improves the import speed for me.