MYSQL VARCHAR or CHAR for fixed length string

Question

If I know a value being stored in MySQL is always going to be exactly 32 characters, is it better performance to make the column type CHAR instead of VARCHAR? What exactly is the performance difference between using VARCHAR and CHAR?

Thanks.

Solution 1

I would use CHAR.

If you are likely to search on the column, CHAR presents a small performance upgrade over VARCHAR.

Since your data size is going to be fixed, there is no downside to using CHAR, as an VARCHAR equivalent will store anywhere from one to two bytes as a prefix.

Reference: MySQL CHAR vs VARCHAR

Solution 2

The answer to your first question, "is it better performance to make the column type CHAR instead of VARCHAR?"... is yes. Definitely.

If you ALWAYS know the length is going to be 32, then you should definitely use CHAR.

Several good answers were also given here: Why would I ever pick CHAR over VARCHAR in SQL?

Solution 3

VARCHAR columns use a one or two byte prefix to store the length:

In contrast to CHAR, VARCHAR values are stored as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

So, a VARCHAR column will be very slightly larger. If you know it's always going to be exactly 32 characters, then CHAR seems reasonable. In pretty much any case where you're not completely sure of the length, I'd go with VARCHAR.