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