Interesting Things
- One of our clients had a large production issue due to a long-standing bug in Rails with case sensitivity.
Here’s the situation: Rails validates_uniqueness_of has a flag called :case_sensitive. This flag defaults to ‘true’, but can be flipped.
MySQL’s default collation is case-insensitive. As a result, queries will, in general, ignore case unless specifically overridden.
So one might imagine that setting :case_sensitive to false would be completely harmless in a standard MySQL application.
One would be wrong. Setting case_sensitive to false changes the query to lowercase the field in question, causing the MySQL database to ignore any indices it may have and turning the validates_uniqueness_of operation from something cheap and quick to something requiring a full table scan.
The open Lighthouse ticket on this issue is:
https://rails.lighthouseapp.com/projects/8994/tickets/2503-validates_uniqueness_of-is-horribly-inefficient-in-mysql
MySQL isn’t that great a DB. It cannot do indices on expressions.
However, in PostgreSQL this can be solved easily with:
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
from http://www.postgresql.org/docs/8.4/static/indexes-expressional.html
Then PG will use that index when searching on a lowered column.
One of the many reasons we use PostgreSQL for our projects here at SmartLogic.
May 21, 2010 at 9:57 am
Solution is already here since 2009 ;)
http://pragmatig.com/2009/12/11/validates_uniqness_of-mysql-slow
May 21, 2010 at 12:17 pm