Re: [jira] Created: (CAY-1210) mysql does not use index for case insensitive searches

From: Andrus Adamchik (andru..bjectstyle.org)
Date: Fri Apr 24 2009 - 02:46:54 EDT

  • Next message: Andrus Adamchik: "Re: [jira] Created: (CAY-1214) Cayenne should help ensure that callback methods are implemented"

    On Apr 24, 2009, at 9:21 AM, Aristedes Maniatis wrote:

    > * Binary collation (which is non-standard)

    non-default != non-standard. Also note that there are binary and there
    are character case-sensitive collations (e.g. "latin1_general_cs"). I
    think it is reasonable to expect a user to pick a *_cs collation.
    After all, most other DB's are using *_cs collations as a default.

    > * Explicit attempt to perform non-case sensitive search (which
    > questions why they chose a binary collation)

    Maybe because they are doing both types of searches. Also *_cs
    collations affect ORDER BY if I am not mistaken, which can be
    important, independently from search. Essentially if we stop using
    UPPER for MySQL, we'll address one important case at the expense of
    another.

    As it seems that any solution here is going to be a compromise, I
    guess it has to be implemented as a flag in MySQLAdapter that defines
    one or the other strategy. AutoAdapter can set this flag based on the
    database default collation:

    ==> show variables like "collation_database";

    If a user needs different behavior, they will have to set it manually
    per adapter.

    Andrus



    This archive was generated by hypermail 2.0.0 : Fri Apr 24 2009 - 02:47:26 EDT