File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes index on username or on a surrogate key ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "index on username or on a surrogate key ?" Watch "index on username or on a surrogate key ?" New topic

index on username or on a surrogate key ?

Hussein Baghdadi
clojure forum advocate

Joined: Nov 08, 2003
Posts: 3479

My application uses surrogate keys for my domain entities.
User class contains 'username' instance variable which it is guarnteed to be unique at the database level (through SQL constraint).
The application never use the id field to inquire any saved User object since it has no business meaning, but it uses username column alot.
So, whats better ?
creating index on id column ? or on username column ?
Yes, sounds like a stupid question (username is an obvious choice), but I'm not SQL guru.
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 862
Do you use either column as a foreign key in another table? If so, the surrogate key could be used in joins and if you ever want to change a user name (say due to a typo) it would be easier to change. You can create unique indexes on both keys, regardless which is your primary key. - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Joan Horta Tosas
Ranch Hand

Joined: Feb 01, 2007
Posts: 59
If I recall correctly, most databases I've used automatically create indexes for primary keys, so the question would only be to create another index for username. My first response is yes, because as you said, you often access your data through this field. In the applications I use to do there are high volumes of data, so it wouldn't be difficult to find more than one index for the same table in our databases, based on the different criterias used to select that data.
I agree. Here's the link:
subject: index on username or on a surrogate key ?
It's not a secret anymore!