Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was reading about relational databases like sqlite and I always see the schema for user storage like:

UserID (index) | Username | Password 

but wouldn't it be actually much faster to use Username as an index?

For example when a user logs in you query WHERE username == some_name, the database engine has to search through all record for this data, right? (namely SQLite? - I am using this)

Why do I always see this "ID" and not just username as the index?

Assuming all names will be unique, what is the point in an integer based index?

What is the actual difference in terms of lookup speed? Or space? (assuming a maximum character count at 24 characters).

Or would it be even better to just make a additional/separate lookup table for username<->userid resolving and then load the data with userid from the main table where the index is the userid?

share|improve this question
    
There are a couple of similar questions. It is called "surrogate key". stackoverflow.com/questions/3747730/… - stackoverflow.com/questions/63090/… - stackoverflow.com/questions/16517262/… and more. –  Stefan Steinegger Jul 10 '13 at 6:55
add comment

migrated from stackoverflow.com Jul 10 '13 at 6:59

This question came from our site for professional and enthusiast programmers.

4 Answers

up vote 3 down vote accepted

There are a lot of places internally where it is possible to reference data using a user id, which is better than a string. For example, if you had 4 tables referencing the user table, they'd each have a string column which is less efficient than 4 integer indexed columns. This is the reason why most schemas use this layout; you're more likely to want to reference a user than to refer to the user by name.

However, you can also set username to be an index, which you might want to do if you are looking it up a lot.

To answer your edited question, a 24 character text field will require roughly 24 bytes. Depending on whether it's a varchar or a char it will be stored differently, but the full amount will be needed for searching, so let's consider them both to be 24.

An integer field will require 4 bytes.

You can see how this will add up over time. For comparisons, assume a linear time comparison, meaning 24 operations to compare two a string field, while only 4 operations to compare an integer field. In practice, they will probably take roughly the same amount of time, though the string comparison cannot be faster.

If you want to search by username, create a table with the schema of username | user_id. That'll let you search by username and join with the other data you are looking for while still being efficient.

share|improve this answer
    
well I don't really know how queries look it up, do they go through evey record in the database? If yes I will definitely create an index for the username, or maybe another table which has index username and column userid.. –  Gizmo Jul 10 '13 at 4:36
    
If the fields they're querying on are not indexed, they might have to search the entire table, otherwise they don't need to. Your second option, the extra table, is probably a good idea-look up 3rd normal form for more information. –  Matt Bryant Jul 10 '13 at 4:38
add comment

User ID's are guaranteed to be unique. Even if a user get's deleted, his ID will never be reused. This means that anything based on ID's will always be the same for a particular user. This is important for data integrity. It is also significantly less space to store an int vs a string (especially for larger numbers) and faster to process.

share|improve this answer
    
He can easily make the username field unique, so the first part of this answer does not really apply. –  Matt Bryant Jul 10 '13 at 4:35
    
well he can, but if I make a user called John, and the user gets deleted, then my friend comes and makes a user called John, it can potentially create problems later when an old report (for example) which is supposed to link to me links to my friends user account instead. The correct response is for it to not find the user it is looking for. Unless he intends to permanently ban any username that has been used before at any point, it still applies. –  PlausibleSarge Jul 10 '13 at 4:38
    
There are use cases for both, though I agree that having a separate userid would be a much better idea. –  Matt Bryant Jul 10 '13 at 4:40
add comment

You can do that...just change the primary key and the sql server will create an index over it.

How ever i don't see why you would do this.

share|improve this answer
    
Yeah I know that I can do that but that is not the question, I want to know if it will give me any performance imporvements over an integer based index or not, and why. –  Gizmo Jul 10 '13 at 4:35
    
In theory the int should be faster...but the biggest diffrence would be that indexing names would take up more space then indexing ints. –  Kevin Boss Jul 10 '13 at 4:41
add comment

I wouldn't use username as a PK, not just for arguable performance reasons, but because the username represent 50% of the credentials. If you need to log the user activity, export data, send a user reference to a Third Party, etc. it is more secure to use an ID then the customer username.

share|improve this answer
    
Security by obscurity does not mean it's more secure. –  Matt Bryant Jul 10 '13 at 4:47
    
a application which just stores playernames from a server and money/kill statistics isn't really going to need that kind of "security". –  Gizmo Jul 10 '13 at 4:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.