On Aug 11, 2008, at 4:30 AM, Gregory Stark wrote:
"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...
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@xxxxxxxxxxxxxx
)
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@xxxxxxxxxxx
web: http://www.rvantwisk.nl/
skype: callto://r.vantwisk
