Subject Re: [firebird-support] Re: Is this possible ?
Author Ann W. Harrison
svanderclock wrote:
> in fact the structure of the table would be
> Table Colors (
> ID Varchar 50 not null,
> A SMALLINT not null,
> B SMALLINT not null,
> C SMALLINT not null,
> ...
> Y SMALLINT not null
> )
> now i will do only this kind of query :
> select ID from table colors where A > xxx - 10 and A < xxx + 10
> and b > nnn - 10 and b < nnn + 10 ....
> and y > www - 10 and y < www + 10
> only this exactly kind of query !
First, for the 25 fields A - Y, I would use a CHAR(1) field with
the character_set OCTETS. That will give you a well behaved
single byte binary field - one that doesn't make any assumptions
about space characters, etc.

Second, if you know the ID will always contain exactly 25 bytes,
make it CHAR(25) also character set OCTETS.

As an aside, don't try to bundle two fields worth of data into a
single small int field because all integers are signed and the
high order bit will screw up your inequality comparisons.

Third, the only sane (and not very sane) index strategy is one
index per field, in addition to the primary key index. The problem
is that Firebird can't use the second and subsequent fields as
indexes if the first field's qualification isn't equality, which it
isn't. So compound keys aren't useful. That's going to be a lot
of overhead when you add or change records.

Best regards,