--- On Mon, 11/8/08, Gregory Stark <stark@xxxxxxxxxxxxxxxx> wrote: > From: Gregory Stark <stark@xxxxxxxxxxxxxxxx> > Subject: Re: [PERFORM] Using PK value as a String > To: "Jay" <arrival123@xxxxxxxxx> > Cc: pgsql-performance@xxxxxxxxxxxxxx > Date: Monday, 11 August, 2008, 10:30 AM > "Jay" <arrival123@xxxxxxxxx> 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... > I don't understand Gregory's suggestion about the design. I thought using natural primary keys as opposed to surrogate ones is a better design strategy, even when it comes to performance considerations and even more so if there are complex relationships within the database. Regards, Valentin > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's On-Demand Production > Tuning > > -- > Sent via pgsql-performance mailing list > (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance __________________________________________________________ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html
