Il 29/08/2012 18.09, Chris Angelico ha scritto:
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
Yes I need case-insensitive comparision and the best way to (optionally) obtain it is without alter all application queries that already works with other DB engine (!!)
My simply questions are:
1) Can be a custom collation a solution for my needs ?
2) How can create a custom collation (... what steps ...) ?
Thanks.