0

From the notes of week 9 - lecture, continued,

Presumably, username is unique, so why bother with an id column? An id is only 32 bits because it’s an integer, whereas username is a variable-length string. Comparing strings is not as fast as comparing integers, so lookups by id will be faster than lookups by username.

I understand the idea of an id (a number) being of a fixed-length while a username (a string) is of a variable-length and thus, id comparisons will be faster most of the time.

But how do we get the id at the first place to use it in searching?

I know we can create a separate unique column to store IDs, but I mean when we pass the username/password combination, what does that have to do with searching with using an id? How do we make use of the advantage of searching with an ID without knowing it first?

1

When looking up the user by username/password, the id field will not be used, and it will not help in such situations.

Where the id is useful is where you want to find a specific user when you do know their id. For example, if you have users in a list which you can select, you would use the selected id in the SQL query, and not their username. As you mentioned searching by the fixed size number will be faster than searching by their username.

Typically the username/password lookup will be performed only once during login to obtain the user's id, or a random token number which is associated with their id. In a login scenario the extra delay is acceptable as it occurs seldom.

I think the intention of bringing up this distinction in the lecture is to teach students to avoid relying solely on strings as a unique key in a database, since it is so much slower compared to numbers.

A real world example of such misuse is where the programmer will store a hash string, typically using MD5 or SHA, and use that to lookup users or other data.

This is not to say that strings should never be used as a lookup. It is possible to improve lookup performance by creating an index on the field, which results in SQL maintaining something akin to a trie, a hash table, or a b-tree to improve lookup performance, at the expense of taking extra space.

| improve this answer | |
  • 1
    I actually wasn't talking about the username/password example specifically. I probably also knew that, but I just wanted to make sure since it wasn't clear in the lecture. Thanks a lot! :) – Kareem Aug 7 '14 at 14:02
  • Sorry I didn't fully understand the question then. I saw you accepted the answer already, but if you want to maybe add another example to the question, I'll see if I can explain it another way? – Luke Van In Aug 7 '14 at 14:06
  • No no, the answer is perfect. I mean I was just talking about the idea of having an id and using it to search, in general, without seeing it explicitly present as an input. – Kareem Aug 7 '14 at 14:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .