|Subject||Re: Creating index problem with collation varchar field|
> i used this domain on a fieldI think this error message is actually correct in this case. There is
> i must create an index on this field
> CREATE INDEX INDEX1 ON TABLE (STR100FIELD);
> 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
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
where ReallyBigField = :SomeLongString
and HashOfReallyBigField = MD5(:SomeLongString)