speed-up

Speeding up Authlogic login for Postgres and MySQL

| 1 Comment

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:

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):

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:

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:

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

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.

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:

Checking out how it performs with EXPLAIN:

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.
Share
* Railsware is a premium software development consulting company, focused on delivering great web and mobile applications. Learn more about us.
  • Ben Johnson

    I released a new verison that addresses is 3.3.0

Want to get more of Railsware blog?

RSS FEED

We're always ready to help!

CONTACT US