|Subject||RE: [firebird-support] Re: Is this possible ?|
> -----Original Message-----A VarChar(1) field would actually use 3 bytes on disk.
> From: email@example.com [mailto:firebird-
> firstname.lastname@example.org] On Behalf Of svanderclock
> Sent: June-01-10 1:39 PM
> To: email@example.com
> Subject: [firebird-support] Re: Is this possible ? - Email found in subject
> yes, i m just sorry that their is not 8bit Field :(
> do you thing i can store them in Varchar(1) field ?
> can i store in varchar any char from #0 to #255 ?Only if you use the Octal charset.
> after is the instruction where myVarchar1field > xxx will still work correctly ?Without the proper Charset setting, I suspect that the logic would have problem when value = #32 (space character) since by SQL standard space is not a significant character for comparison purposes.
Personally, I would simply defined the fields as SMALLINT (range = -32,767 to 32768) and be done with it.
As for indexing, if you know that some fields will *always* be used in your searches, I would not define any non-PK/FK indexes. With the right server cache and HDD/SDD setup, it would be faster to perform a NATURAL scan of the table then to use *any* index for query purposes.
The lack of secondary indexes will also mean faster write IO speeds for table updates (no extra indexes to maintain), a smaller database size and much faster database restores (such you need to perform one)
If you consider that 50 columns of SMALLINTs would only use 100Bytes and assuming a you have an INTEGER for a KEY and 1 other INTEGER for a FK, the maximum records size would be about 132 bytes (Ann H, please confirm). Using SMALLINTs 50 million rows would fit into 416,666 16KB pages, or about 6GB.
So, as Derryck suggested, get a 64Bit OS with about 12GB of RAM and define small FB cache and let the OS take responsibility for the cache management.