Update

(2012, 10 December) Authologic-team patched weird behavior with (I)LIKE and now Postgres users can make everything without clutches. Just use new version 3.2.0.

Introduction

User authentication – is the most utilized part of Authlogic gem. But default configuration of current version(3.1.2) doesn’t use database indexes or their usage is poor.

Even so, this can be easily fixed for MySQL and with some efforts for Postgres.

Case-insensitive Login

Main reason for slow authentication – is that by default Authlogic uses case-insensitive login. For versions < 3.0.1 it fetches user record with next query:

SELECT  "users".* FROM "users"  WHERE (LOWER("users".email) = 'username@gmail.com') LIMIT 1;

In this case Postgres and MySQL don’t use index for email field and do sequence scanning on the whole users table.

To address it in version 3.0.1 Authlogic switched to “faster query”, but in fact this made things even worse. Consider next query for Postgres(for MySQL it’s plain LIKE):

SELECT  "users".* FROM "users"  WHERE ("users".email ILIKE 'username@gmail.com') LIMIT 1;

MySQL benefits from LIKE and optimize search, but for Postgres ILIKE doesn’t use indexes, so it’s just meaningless change for it.

Disaster bug with (I)LIKE

With optimization this change introduced subtle bug. If user account contains ‘_’ character or even worse ‘%’, LIKE use it, because Authlogic doesn’t escape these symbols.

It leads to weird behavior. If you have accounts like: user1@gmail.com, and user_@gmail.com, second user will not be able to login if user1 is stored before user_, because database matches user1 in firsthand.

But everything can be optimized without usage of LIKE for both databases.

Correct settings for MySQL

MySQL users are lucky. Know you or not, but usualy MySQL compares strings in case-insensitive manner. Just check collation for database:

mysql> SELECT collation(version());
+----------------------+
| collation(version()) |
+----------------------+
| utf8_general_ci      |
+----------------------+

If collation ends with _ci-suffix, it’s case-insensitive and you don’t need any LOWER() or LIKE clutches. Plain User.find does a trick. To activate it, disable case-insensitive mode:

class User < ActiveRecord::Base
  # ...
  acts_as_authentic do |c|
    c.validates_uniqueness_of_email_field_options case_sensitive: true
    c.validates_uniqueness_of_login_field_options case_sensitive: true
    # ... other settings
  end
  # ...
end

Try to login in development mode and check how Authlogic fetches user-record. It should be something like:

SELECT "users".* FROM "users" WHERE "users"."email" = 'username@gmail.com' LIMIT 1

Even if your database collation isn’t _ci, you can easily setup custom collation for any column.

This is most simplest and plain solution for MySQL, so stick to it.

Configuration for Postgres

Sadly with current version of Authlogic it’s just impossible to optimize login for Postgres. To make it happens, ILIKE should be reverted back to LOWER-based select. For using LOWER-based search, update Authlogic to version >= 3.2.0.

gem 'authlogic', '3.2.0'

Now, after switching to LOWER it’s possible to use Postgres ability to build indexes on expressions.
This migration creates an index on LOWER(email) expression:

class CreateIndexOnLowerEmailInUsersTable < ActiveRecord::Migration
  def up
    execute <<-SQL
      CREATE INDEX index_users_lower_email ON users (LOWER(email))
    SQL
  end

  def down
    execute <<-SQL
      DROP INDEX index_users_lower_email
    SQL
  end
end

Checking out how it performs with EXPLAIN:

EXPLAIN SELECT  "users".* FROM "users"  WHERE (LOWER("users".email) = 'nickname@gmail.com') LIMIT 1;
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Limit  (cost=0.00..8.48 rows=1 width=1903)
   ->  Index Scan using index_users_lower_email on users  (cost=0.00..8.48 rows=1 width=1903)
         Index Cond: (lower((email)::text) = 'nickname@gmail.com'::text)

Now query uses index on LOWER(email) expression. In our production environment difference between non-indexed(ILIKE) and indexed(LOWER+index) solutions is about 10x times faster for indexed-version.

Summary

• Authlogic uses flawed default configuration for login-part
• MySQL users can configure optimal behavior without a fuss
• Postgres users should patch Authlogic-gem use authlogic-gem >= 3.2.0 to apply expression indexes and gain better performance
• Current Authlogic seach by (I)LIKE introduced bug and should be reverted to LOWER-based. You can support this change in comments to appropriate pull-request. Pull-request is applied and can be used in version 3.2.0.