CHAR() or VARCHAR() as primary key in an ISAM mySQL table?

Question

I need a simple table with a user name and password field in mySQL. Since user names must be unique, it makes sense to me to make them the primary key.

Is it better to use CHAR() or VARCHAR() as a primary key?

Answer 1

may as well just use a user ID index, it's much faster for joins vs char/varchar. the two seconds it takes to add that now could save you a lot of time later if you accidently have to expand the functionality of your schema.

some pitfalls to think about:

• say we add a few tables at a future date, what if someone wants to change a username?
• say the app is more successful then we think, and we have to look at optimization, do you really want to redo your schema at this point to reduce the overhead of a varchar'ed index?

Answer 2

I don't see CHAR used much in any MySQL databases i've worked on. I would go with the VARCHAR

For a CHAR(30) for example, the entire 30 characters are stored in the table meaning every entry will take up the same space, even if your username is only 10 characters long.

Using VARCHAR(30) it will only use enough space to store the string you have entered.

On a small table it wont make much difference, but on a larger table the VARCHAR should prove to keep it smaller overall.

Answer 3

I would work hard to NOT use CHAR() or VARCHAR() as a PK but use an int with an auto_increment instead. This allows you to use that user_id in child tables if needed and queries on the PK should be faster. If you have to use either a CHAR() or VARCHAR(), I'd go with the CHAR() since it's a fixed width.

I'm not 100% sure how MySQL deals with VARCHAR()'s but most database engines have to do some magic under the hood to help the engine know where the VARCHAR() fields ends and where the next field begins, a CHAR() makes it straight forward and keeps the engine from having to think to much.

Answer 4

[I would work hard to NOT use CHAR() or VARCHAR() as a PK but use an int with an auto_increment instead.] +1

Put a unique constraint of the username but use the int field as the PK