cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrew Lindesay <...@lindesay.co.nz>
Subject Re: (CAY-1210) mysql does not use index for case insensitive searches
Date Tue, 11 Jan 2011 11:18:47 GMT
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