Re: How long is long enough for primary key ?

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 07 Oct 2006 09:57:59 -0700
Message-ID: <1160240277.267828@bubbleator.drizzle.com>

krislioe_at_gmail.com wrote:
> Hi,
>

>> Length is irrelevant.

> I thougt that the long primary key is bad for performance, isn't it ?
>
> And the long PK is the reason for using surrogate key, isn't it.
>
> Thank you,
> xtanto

While it is true that a numeric comparison will probably be faster than a string comparison there are many other considerations.

First ... you have no control over the length of a natural key. It is what it is.

Second ... if you create a surrogate key you add overhead with the creation and use of the surrogate and you create overhead with the then required maintenance of a unique constraint and index on the natural key too.

I wouldn't lose one minutes sleep over the length of a primary key unless I had a specific performance issue that, after optimizing everything else, was related to that specific string comparison.

I've seen a lot of slow systems that required tuning. Not once have I seen the root cause be the use of a natural key.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Oct 07 2006 - 11:57:59 CDT