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 12:04:30 GMT
Yeah, a DB-side solution would have been ideal here, and I am really reluctant about adding
a new modeling abstraction. However I still don't see a clean solution for writing efficient
cross-DB CI LIKE code in Cayenne (as MySQL won't support expressions in indexes).

Maybe do a total hack to get us out of this limbo - how about a DI extension point for CI
LIKE SQL generation strategy (or rather a System property activating this strategy - 'default
collation case sensitivity')? This won't complicate the mapping, won't force us to generalize,
and will allow the same mapping to be used with both kinds of schemas.

Andrus


On Jan 11, 2011, at 1:18 PM, Andrew Lindesay wrote:
> Hello;
> 
> PG also appears to support functional indexes;
> 
> http://www.postgresql.org/docs/9.0/interactive/indexes-expressional.html
> 
> I use functional indexes with Oracle for this purpose.
> 
> > ...the problem is the the dba has to know to add these additional
> > indexes.
> ...
>> we can think of this as a different field type in the modeler. VARCHAR
>> and ciVARCHAR, etc. After all, 'case insensitive' is just as important a
>> concept as the difference between BLOB and TEXT.
> 
> I can see a bit of a problem arising with this ^^^ solution where somebody wants to be
CS sometimes and CI at other times -- case sensitivity is often an option in searches.  By
using an explicit column type, the choice appears to be one way or other at the database level.
> 
> I think the functional indexes do make sense and it seems reasonable that a DBA would
apply them as part of tuning.  Maybe it would be best to have a page in the manual on this
explaining that for MySQL/... CI search it is best to change the collation on the schema objects
and for Oracle/PG/... CI search it is best to implement functional indexes?
> 
> cheers.
> 
> -- 
> Andrew Lindesay
> www.silvereye.co.nz
> 


Mime
View raw message