Close
Glad You're Ready. Let's Get Started!

Let us know how we can contact you.

Thank you!

We'll respond shortly.

PIVOTAL LABS
Standup 5/21/2010: validates_uniqueness_of and case sensitivity

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

Comments
  1. 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.

Post a Comment

Your Information (Name required. Email address will not be displayed with comment.)

* Copy This Password *

* Type Or Paste Password Here *