[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Using PK value as a String
From:       ries van Twisk <pg () rvt ! dds ! nl>
Date:       2008-08-11 12:22:04
Message-ID: EDC7C945-26DD-4652-8BF8-F925284519F6 () rvt ! dds ! nl
[Download RAW message or body]


On Aug 11, 2008, at 4:30 AM, Gregory Stark wrote:

> "Jay" <arrival123@gmail.com> writes:
>
>> I have a table named table_Users:
>>
>> CREATE TABLE table_Users (
>>   UserID       character(40)  NOT NULL default '',
>>   Username   varchar(256)  NOT NULL default '',
>>   Email          varchar(256) NOT NULL default ''
>>   etc...
>> );
>>
>> The UserID is a character(40) and is generated using UUID function.  
>> We
>> started making making other tables and ended up not really using
>> UserID, but instead using Username as the unique identifier for the
>> other tables. Now, we pass and insert the Username to for  
>> discussions,
>> wikis, etc, for all the modules we have developed. I was wondering if
>> it would be a performance improvement to use the 40 Character UserID
>> instead of Username when querying the other tables, or if we should
>> change the UserID to a serial value and use that to query the other
>> tables. Or just keep the way things are because it doesn't really  
>> make
>> much a difference.
>
> Username would not be any slower than UserID unless you have a lot of
> usernames longer than 40 characters.
>
> However making UserID an integer would be quite a bit more  
> efficient. It would
> take 4 bytes instead of as the length of the Username which adds up  
> when it's
> in all your other tables... Also internationalized text collations  
> are quite a
> bit more expensive than a simple integer comparison.
>
> But the real question here is what's the better design. If you use  
> Username
> you'll be cursing if you ever want to provide a facility to allow  
> people to
> change their usernames. You may not want such a facility now but one  
> day...
>

If you generate UUID's with the UUID function  and you are on 8.3,
why not use the UUID type to store it?

Ries


> -- 
>  Gregory Stark
>  EnterpriseDB          http://www.enterprisedb.com
>  Ask me about EnterpriseDB's On-Demand Production Tuning
>
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org 
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Ries van Twisk
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS  
WebORB PostgreSQL DB-Architect
email: ries@vantwisk.nl
web:   http://www.rvantwisk.nl/
skype: callto://r.vantwisk




-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[prev in list] [next in list] [prev in thread] [next in thread]