Il 29/08/2012 18.09, Chris Angelico ha scritto:
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 (!!)On Thu, Aug 30, 2012 at 1:56 AM, Nicola Cisternino <ncister@xxxxxxxxxx> 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 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. |
