[Cayenne-dev] Re: [jira] Created: (CAY-1210) mysql does not use index for case insensitive searches

Well, there are two ways in MySQL to force case sensitive searches.
Either by picking a binary collation for the text field, or by
changing the column type from TEXT/VARCHAR to BLOB/CLOB. The beauty of
the latter is that Cayenne knows about the datatype and so can behave
differently. But Cayenne knows nothing about collations (perhaps it
should?).

So there is a workaround to the problem you raise. But there is no
workaround to getting case-insensitive searches (which in our use
cases are 99.9% of searches on text fields) to use an index.

The only case where existing behaviour changes due to this
modification is if:

* Binary collation (which is non-standard)
* Explicit attempt to perform non-case sensitive search (which
questions why they chose a binary collation)

The recommendation for users is to change that column to CLOB and
current behaviour continues.


What do you think?


Ari Maniatis


-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A

non-default != non-standard. Also note that there are binary and there
are character case-sensitive collations (e.g. "latin1_general_cs"). I
think it is reasonable to expect a user to pick a *_cs collation.
After all, most other DB's are using *_cs collations as a default.
Maybe because they are doing both types of searches. Also *_cs
collations affect ORDER BY if I am not mistaken, which can be
important, independently from search. Essentially if we stop using
UPPER for MySQL, we'll address one important case at the expense of
another.

As it seems that any solution here is going to be a compromise, I
guess it has to be implemented as a flag in MySQLAdapter that defines
one or the other strategy. AutoAdapter can set this flag based on the
database default collation:

==> show variables like "collation_database";

If a user needs different behavior, they will have to set it manually
per adapter.

Andrus