At 01:27 PM 12/11/2004, Alexander Gräf wrote:

>No, use 64 bit BIGINT. Only use CHAR(xy) CHARACTER SET OCTETS if you have
>more than 8 bytes to store. For example, to give you an example of what
>needs to be done to compare to values (pseudo code):
>
>Int64 val1, val2;
>If (val1==val2) ....
>
>Vs.
>
>Byte val1[16], val2[16];
>If (memcmp(val1, val2, 16) == 0) ...
>
>First approach is much faster,

Firebird indexes store keys using prefix and suffix compression
and compare the values bytewise, regardless of type. Keys are not
aligned on any particular boundary and are constructed for bytewise
comparisons. Simple rules like your pseudo-code are not relevant
to an index comparison.


For example, an eight byte string field containing the values
"AAAABBBB", "AAAABBBC", "AAAABBBD", "AAAABBBE", "AAABBBAB" would
compress to this : "0AAAABBBB", "7C", "7D", "7E", "3BBBAB". If
you were looking for "AAABBBAA", you'd start with the first value
on the list, then look at the 7 at the beginning of the next key
and know it couldn't be a match, look at the next 7 and know it
couldn't match, then check and skip the next key because it starts
with a 7. Since your value does match the first three bytes of
the first key, you compare against the tail of the fifth value
which is larger than your search value and quit.

In other words, the speed of an index depends on the size of the
key and how much it can be compressed. The compression works
best if the least selective part of the key is first.

Regards,

Ann