cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: (CAY-1210) mysql does not use index for case insensitive searches
Date Tue, 11 Jan 2011 10:07:20 GMT
As an aside we should close the hole on the opposite end - Cayenne CS LIKE. MySQL adapter should
use BINARY keyword to ensure that CS matching is performed. I am personally using CS LIKE
as a hack around CAY-1210, but that of course makes Cayenne code non-portable.

Andrus

On Jan 11, 2011, at 11:58 AM, Andrus Adamchik wrote:

> I am taking this to dev, as there's still no clarity in my mind on how to approach it.
> 
> So the goal here is to optimize case-insensitive (CI) LIKE, and the issue is that MySQL
doesn't support indexes on transformed columns (e.g. "UPPER(column)"), while it does support
CI column collations, and hence - CI indexes and CI LIKE. Behavior of other DBs is unknown.
Oracle supports UPPER(column) indexes, but beyond that we haven't researched it. Suggested
solutions come down to either mapping collation behavior per DbAttribute in Cayenne, or trying
to determine it in runtime via DB metadata. 
> 
> My concern with former is that Cayenne model will have too much information about the
underlying DB, not directly relevant to ORM. So if we are to go this way, we need to first
demonstrate how this info may be used with other databases. E.g if we find that LIKE can be
optimized for at least 2-3 other major DBs by taking collation case-sensitivity into account,
then this solution may be worth pursuing. 
> 
> The later approach may incur some overhead. Each LIKE query will have to determine whether
the columns involved are CI or CS. One way to do it in MySQL is this:
> 
> select collation(c1) from t1 limit 1;
> +-------------------+
> | collation(c1) |
> +-------------------+
> | utf8_general_ci   | 
> +-------------------+
> 1 row in set (0.02 sec)
> 
> I guess with metadata caching, the overhead may be reduced to a bearable minimum. Not
sure if that's the easiest approach?
> 
> Also maybe there are other ways to solve this problem (maybe even outside Cayenne, just
like that "UPPER(column)" index idea)?
> 
> Thoughts?
> 
> Andrus
> 
> 
> On Jan 10, 2011, at 11:45 AM, Ari Maniatis (JIRA) wrote:
>>   [ https://issues.apache.org/jira/browse/CAY-1210?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12979519#action_12979519
] 
>> 
>> Ari Maniatis commented on CAY-1210:
>> -----------------------------------
>> 
>> I'd like to solve this one since it also affects other database types and is a bit
of a performance issue for us right now. What about this as a solution:
>> 
>> * Add new column to Cayenne modeler in the dbAttribute called "case sensitive". It
is a checkbox which is ticked by default (to preserve existing behaviour).
>> * Change the SQL generator to not spit out UPPER for those fields
>> 
>>> 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
>>>        Components: Database integration
>>>          Reporter: Ari Maniatis
>>>          Assignee: Ari Maniatis
>>>           Fix For: 3.1M1
>>> 
>>> 
>>> 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