This week's book giveaway is in the Cloud/Virtualization forum.
We're giving away four copies of Learning OpenStack Networking: Build a solid foundation in virtual networking technologies for OpenStack-based clouds and have James Denton on-line!
See this thread for details.
Win a copy of Learning OpenStack Networking: Build a solid foundation in virtual networking technologies for OpenStack-based clouds this week in the Cloud/Virtualization 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:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

index on username or on a surrogate key ?  RSS feed

 
clojure forum advocate
Bartender
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.
 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!