Primary Key Performance with INTEGER vs. VARCHAR

Поиск
Список
Период
Сортировка
Искать

Primary Key Performance with INTEGER vs. VARCHAR

От:
Siah <siasookhteh@gmail.com>
Дата:
Hi,

Designing my application, I was wondering if having my primary keys
(to be indexed) with VARCHAR brings performance down significantly? My
own test didn't show much difference. Thinking about it though, I'd
guess Integer Indexing should be much quicker and efficient.

I'd appreciate all comments in this regard,
Mike

Re: Primary Key Performance with INTEGER vs. VARCHAR

От:
Jim Nasby <decibel@decibel.org>
Дата:
On Jul 21, 2007, at 5:49 AM, Siah wrote:
> Designing my application, I was wondering if having my primary keys
> (to be indexed) with VARCHAR brings performance down significantly? My
> own test didn't show much difference. Thinking about it though, I'd
> guess Integer Indexing should be much quicker and efficient.

Generally, anything dealing with a text/varchar field is going to be  
slower than on, say, an integer. But remember the first rule of  
performance tuning: don't. Do you *really* need to worry about the  
extra overhead of varchar vs int? Probably not.

What you *should* be thinking about is do you really want a varchar  
PK? Generally speaking, it's better to use a phantom PK (ie: a  
SERIAL), and put a UNIQUE constraint on the varchar in the  
appropriate table.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Re: Primary Key Performance with INTEGER vs. VARCHAR

От:
"Merlin Moncure" <mmoncure@gmail.com>
Дата:
On 7/21/07, Siah  wrote:
> Hi,
>
> Designing my application, I was wondering if having my primary keys
> (to be indexed) with VARCHAR brings performance down significantly? My
> own test didn't show much difference. Thinking about it though, I'd
> guess Integer Indexing should be much quicker and efficient.

Integer indexes are usually quicker to build and smaller which
translates to speed (better utilization of o/s cache).  The difference
between a index lookup in cache/not in cache is much bigger than index
lookup integer/text.  OTOH, text natural keys save you an index you
often have to make anyways and can optimize out joins in some cases.
so the answer is, 'it depends' :-)

merlin
FAQ