On Thu, Aug 30, 2012 at 1:56 AM, Nicola Cisternino <ncister@tiscali.it> wrote:
> The same query using " .... LIKE <value> ...." is completed in 15 ms while
> using " .... ILIKE <value> ...." the execution time is 453 ms ....
Sounds to me like (pun not intended) there's an index that's being
used in one case and not in the other.
But taking this back a step: Do you really need case-insensitive
comparisons? They become pretty much impossible once you start looking
at internationalization. Sure, it's easy in ASCII. You just mask off
one bit and off you go. But truly case insensitive matching gets
really hairy. Can you redo things with case sensitive searches,
possibly with some forcing of case in simple situations? For instance,
you accept a name prefix from the user, look at it and find that it's
all ASCII; lower-case it, then upper-case the first letter, then add a
percent sign, and use a case-sensitive LIKE. That's going to produce
correct results in most cases, and is way faster than truly case
insensitive searching.
ChrisA