cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ari Maniatis (JIRA)" <j...@apache.org>
Subject [jira] Commented: (CAY-1210) mysql does not use index for case insensitive searches
Date Fri, 24 Apr 2009 07:08:36 GMT

    [ https://issues.apache.org/jira/browse/CAY-1210?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12702266#action_12702266
] 

Ari Maniatis commented on CAY-1210:
-----------------------------------

>From a conversation with Andrus, the following problem was identified: users using non-default
collations *_bin or *_cs will have their behaviour changed by this change

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.

Alternatively, Cayenne modeler will need to be taught about collations so they can be stored
against the dbcolumn. I don't think this is a MySQL specific problem, since collation affects
the behaviour of other databases. It is just that MySQL doesn't give us a workaround with
being able to create a functional index on UPPER(name).

For what it is worth, we have this identical problem in WebObjects and need to find a similar
fix there.

> mysql does not use index for case insensitive searches
> ------------------------------------------------------
>
>                 Key: CAY-1210
>                 URL: https://issues.apache.org/jira/browse/CAY-1210
>             Project: Cayenne
>          Issue Type: Improvement
>            Reporter: Ari Maniatis
>            Assignee: Ari Maniatis
>             Fix For: 3.0
>
>
> When performing a case insensitive search Cayenne spits out SQL which looks like this
> SELECT .... WHERE upper(name) LIKE upper("fred")
> This prevents any index being used for the search. Since mysql already performed case
insensitive searches on text fields we need to suppress the 'upper' functions being used in
these situations. All searches on these fields are already case insensitive.
> http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message