(2012, 10 December)<\/em> Authologic-team patched weird behavior with (I)LIKE<\/a> and now Postgres users can make everything without clutches. Just use new version 3.2.0.<\/p>\n\n\n\n User authentication – is the most utilized part of Authlogic gem<\/a>. But default configuration of current version(3.1.2) doesn’t use database indexes or their usage is poor.<\/p>\n\n\n\n Even so, this can be easily fixed for MySQL and with some efforts for Postgres.<\/p>\n\n\n\n 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:<\/p>\n\n\n\n In this case Postgres and MySQL don’t use index for email<\/em> field and do sequence scanning on the whole users<\/em> table.<\/p>\n\n\n\n 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<\/em>):<\/p>\n\n\n\n MySQL benefits from LIKE<\/em> and optimize search, but for Postgres ILIKE<\/em> doesn’t use indexes<\/a>, so it’s just meaningless change for it.<\/p>\n\n\n\n With optimization this change introduced subtle bug. If user account contains ‘_’ character or even worse ‘%’, LIKE<\/em> use it, because Authlogic doesn’t escape these symbols.<\/p>\n\n\n\n It leads to weird behavior. If you have accounts like: user1@gmail.com<\/em>, and user_@gmail.com<\/em>, second user will not be able to login if user1<\/em> is stored before user_<\/em>, because database matches user1<\/em> in firsthand.<\/p>\n\n\n\n But everything can be optimized without usage of LIKE<\/em> for both databases.<\/p>\n\n\n\n MySQL users are lucky. Know you or not, but usualy MySQL compares strings in case-insensitive manner. Just check collation for database:<\/p>\n\n\n\n If collation ends with _ci<\/em>-suffix, it’s case-insensitive and you don’t need any LOWER()<\/em> or LIKE<\/em> clutches. Plain User.find does a trick. To activate it, disable case-insensitive mode:<\/p>\n\n\n\n Try to login in development<\/a> mode and check how Authlogic fetches user-record. It should be something like:<\/p>\n\n\n\n Even if your database collation isn’t _ci<\/em>, you can easily setup custom collation for any column<\/a>.<\/p>\n\n\n\n This is most simplest and plain solution for MySQL, so stick to it.<\/p>\n\n\n\nIntroduction<\/h2>\n\n\n\n
Case-insensitive Login<\/h2>\n\n\n\n
SELECT \"users\".* FROM \"users\" WHERE (LOWER(\"users\".email) = 'username@gmail.com') LIMIT 1;\n<\/pre>\n\n\n\n
SELECT \"users\".* FROM \"users\" WHERE (\"users\".email ILIKE 'username@gmail.com') LIMIT 1;\n<\/pre>\n\n\n\n
Disaster bug with (I)LIKE<\/h2>\n\n\n\n
Correct settings for MySQL<\/h2>\n\n\n\n
mysql> SELECT collation(version());\n+----------------------+\n| collation(version()) |\n+----------------------+\n| utf8_general_ci |\n+----------------------+\n<\/pre>\n\n\n\n
class User < ActiveRecord::Base\n # ...\n acts_as_authentic do |c|\n c.validates_uniqueness_of_email_field_options case_sensitive: true\n c.validates_uniqueness_of_login_field_options case_sensitive: true\n # ... other settings\n end\n # ...\nend\n<\/pre>\n\n\n\n
SELECT \"users\".* FROM \"users\" WHERE \"users\".\"email\" = 'username@gmail.com' LIMIT 1\n<\/pre>\n\n\n\n
Configuration for Postgres<\/h2>\n\n\n\n
Sadly with current version of Authlogic it’s just impossible to optimize login for Postgres. To make it happens, ILIKE<\/em> should be reverted back to LOWER<\/em>-based select.<\/s> For using LOWER<\/em>-based search, update Authlogic to version >= 3.2.0.<\/p>\n\n\n\ngem 'authlogic', '3.2.0'\n<\/pre>\n\n\n\n