How Are Lookups by id Faster than Lookups by username?

Question

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?

Answer 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.