Apple

Hi folks,

my experiences of this are with a release of Sybase where case-insensitive prefix searching of indexed columns caused table-scanning of a very big table, and assorted releases of MySQL with case-insensitive collation sequences. I used to use the EOEditingContext delegate API to spot that a fetch was about to occur, and possibly reconstruct the qualifier. The SQL my custom qualifier code was generating (WO451 + Sybase, about 6 years ago ... today, WO451 + MySQL) would have looked something like this:

((emailAddress >= 'P' AND emailAddress < 'Q') OR (emailAddress >= 'p' AND emailAddress < 'q'))
AND
(lower(emailAddress) = 'email@hidden’)

and it worked very well for us because the query plans used indexes as intended, but, noting the comments elsewhere on this thread about collation sequences for alphabetic characters with diacritics.

-- Patrick

From: webobjects-dev-bounces+pmiddleton=email@hidden <webobjects-dev-bounces+pmiddleton=email@hidden> on behalf of Morris, Mark <email@hidden>
Sent: Thursday, February 25, 2016 3:16 PM
To: Lon Varscsak
Cc: WebObjects Development
Subject: Re: Use case for a "case insensitive equals" qualifier

Hi Lon,

We run into this a lot, and I have a rather complex solution for a “case insensitive begins with” qualifier that actually works well, at least with Oracle and its indexing. My method returning this qualifier is actually about 60 lines, but most of that is optimizations for special cases. (We have terabytes of data, so every little bit helps!)

The basic idea, though, is that it only takes a couple of characters to hit on an index and make a huge difference in performance. So…

Trying to case-insensitive-equals match "email@hidden” with the search term “email@hidden”, you would build this sort of qualifier:

(emailAddress like ‘PA*’ OR emailAddress like ‘Pa*’ OR emailAddress like ‘pA*’ OR emailAddress like ‘pa*’) AND UPPER(emailAddress) = UPPER('email@hidden’)

Hope this helps!

Regards,

Mark