Hi. 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. Thanks.
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.
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.
subject: index on username or on a surrogate key ?