Re: What's the most efficient primary key ?

In article <9f0msa$r6h1_at_www.informix.com>, Chris Golledge <chris.golledge_at_informix.com> writes
>Couple of basic thoughts.
>
>Comparison operators, <, >, =, etc. should be a lot faster with integer
>types than with decimals or strings. This would be especially true if you
>were thinking of a string key model that would require where-clause
>conditions similar to "where str_key like '%123456'"; such a pattern match
>would make your indices useless. Whether I went with an int8 or a several
>int4 columns would be determined by whether my OS/hardware platform had
>native support of 64-bit integers or not and whether my data could be
>meaningfully divided into groups.
>

  True.

>Your index model is likely to be just as important, if not more so, than the
>particular data type used for the key column(s). Being able to isolate the
>data reads to a particular section of disk, implying that a clustered index
>or fragmented table is used, is likely to be of more benefit than worrying
>about the number of CPU cycles spent comparing integers rather than strings.
>

  Integer = 4 bytes.
  String "12345678" = char(8) 8 bytes

  1 million rows of integer = 4 Mb to read from disk   1 million rows of char(8) = 8Mb to read from disk

  How can 8Mb be read nearly as fast as 4Mb? Double the disk I/O!

>IMHO,
>Chris
>
>
>"worldonline" <remco.van.toor_at_iquality.nl> wrote in message
>news:9elo7s$sf2$1_at_nereid.worldonline.nl...
>> I want to create billions of unique key, wat's the best option?
>>
>> a compound key containing several integer-columns,
>> a large datatype (decimal)
>> or a string perhaps ??
>>
>> i would like the first options, it is good for using OID's
>>
>>
>> thanks in advance,
>> remco
>>
>>
>>
>
>
 

-- 
David Williams