Subject Re: Creating index problem with collation varchar field
Author Adam
> i used this domain on a field
> i must create an index on this field
> i get this error message
> "key size is too big for index INDEX1"
> i think the problem is about turkish collation and character set
> how can i solve this problem

I think this error message is actually correct in this case. There is
a limit to the size on an index in Firebird, 250something bytes from
memory for 1.5. FB 2 significantly increases this limit again from
memory to some percentage of the page size.

In this case, one option would be to create a surrogate field using a
simple MD5 or SHA hash, and use triggers to create the hash. You could
then index the hashed values, and hash the value you are searching on.
The hash should have very good selectivity.



You would need to write or find a UDF with a simple and fast hash
implementation. (Doesn't have to be strong, just fast to compute)

Triggers would look something like

New.HashOfReallyBigField = MD5(ReallyBigField);

The index could be created on HashOfReallyBigField and would have a
pretty good selectivity (certainly better than a table scan).

Queries would look something like

select *
from TableA
where ReallyBigField = :SomeLongString
and HashOfReallyBigField = MD5(:SomeLongString)