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