MYSQL: varchar vs int for storing a variable

Question

For readability reasons I would like to store a variable as a varchar such as "in", "out", "auto" instead of as a int such as "0", "1", "2". Are there any reasons why I shouldn't store it as a varchar?

The reason I ask is that I have heard that int is faster but does that apply in this scenario or will the speed difference be so small that it is negligible?

I am quite new to MYSQL so please let me know if you need any more information, I have searched for similar questions/answers but haven't found any that answer my question.

Answer 1

There is a simple rule of thumb: If you need to do arithmetic or indexing, use a numeric type, if not use a character type.

As rules of thumb typically depend on the size and angle of the thumb in question, here is what I mean:

• (Classic example #1) Phone number: No arithmetic makes sense, use a VARCHAR and get a free collection of + and () extras
• (Classic example #2) ZIP code: Again no arithmetic, our british friends living in something like LO7SWF will be gratefull, if you use a VARCHAR
• One of a set, e.g. 0-9: If these are the PKs of a lookup table (as they should be), use an int. If their meaning is outside of the DB consider using a VARCHAR (and smile, if the hardware vendor upgrades from strictly-numerical keypads to ones also using # and *)

While it is strictly true, that an index over a VARCHAR field with numerical-only content of 11 figures is slightly slower than an INT(11), I have yet to see the real-world DB, where this makes an appreciable difference.