Re: [ADMIN] Q: Structured index - which one runs faster?

Thanks for replying :)

At 01:00 23.05.2003, Stephan Szabo said:
--------------------[snip]--------------------
>On Thu, 22 May 2003, Ernest E Vogelsinger wrote:
>
>> I need to store an ID value that consists of three numerical elements:
>> - ident1 char(5)
>> - ident2 char(5)
>> - nodeid int4
>
>This seems like a somewhat odd key layout, why char(5) for the first
>two parts if they're numeric as well?

It's not odd - ident1 and ident2 are in fact logical identifiers that _are_
character values, no numbers.

>Generally, you're probably better off with an index on the three columns.
>Otherwise either your clients need to composite the value for the varchar
>column or the system does in triggers for insert/update.

This table will be used by a PHP library accessing it - no direct client
intervention (except the developers and they should know what they're doing ;-)

>Also, what kinds of lookups are you going to be doing? Only lookups based
>on all three parts of the key or will you ever be searching based on parts
>of the keys?

Hmm. Yes, lookups on parts of the keys will be possible, but only from left
to right, ident1 having the highest precedence, followed by ident2 and
finally by nodeid.

These columns will never be modified once inserted. The only operations
these columns will be affected are insert and delete, and lookup of course.
I'm not so concerned with delete since this will not happen too often, but
inserts will, and lookups of course permanently, and both operations must
be as fast as possible, even with gazillions of rows...

--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/