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. Nick Gauthier Nick Gauthier on May 21, 2010 at 09:57AM

    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.

  2. grosser grosser on May 21, 2010 at 12:17PM

    Solution is already here since 2009 ;) http://pragmatig.com/2009/12/11/validates_uniqness_of-mysql-slow