This week's book giveaway is in the Jython/Python forum.
We're giving away four copies of Hands On Software Engineering with Python and have Brian Allbey on-line!
See this thread for details.
Win a copy of Hands On Software Engineering with Python this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • Liutauras Vilda
Sheriffs:
  • Tim Cooke
  • Devaka Cooray
  • Paul Clapham
Saloon Keepers:
  • Tim Moores
  • Frits Walraven
  • Ron McLeod
  • Ganesh Patekar
  • salvin francis
Bartenders:
  • Tim Holloway
  • Carey Brown
  • Stephan van Hulst

index on username or on a surrogate key ?  RSS feed

 
clojure forum advocate
Posts: 3479
Clojure Mac Objective C
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Always! Wait. Never. Shut up. Look at this tiny ad.
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!