Scott Marlowe wrote:
Honestly as a lazy DBA I have to say it'd be pretty easy to write a
script to convert any unique text index into a unique text index with
a upper() in it. As another poster added, collation ain't free
either. I'd say you should test it to see. My experience tells me
that having an upper() (or lower()) index is not a big performance
hit. If the storage of the index would be too much due to large text
fields then make it a md5(lower()) index, which WILL cost more CPU
wise, but allow for > 3k or so of text in a column to be indexed and
cost less IO wise.
Honestly as a lazy DBA I have to say it'd be pretty easy to write a
script to convert any unique text index into a unique text index with
a upper() in it. As another poster added, collation ain't free
either. I'd say you should test it to see. My experience tells me
that having an upper() (or lower()) index is not a big performance
hit. If the storage of the index would be too much due to large text
fields then make it a md5(lower()) index, which WILL cost more CPU
wise, but allow for > 3k or so of text in a column to be indexed and
cost less IO wise.
simple to use tsearch2 (which is case insensitive) or trigram
indexing (for which a similarity search is case insensitive).
-Kevin
