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