CODEWITHSUNDEEP
mysqlcharvarchar
Justin
Justin

Reputation: 45320

MYSQL VARCHAR or CHAR for fixed length string

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.

Upvotes: 15

Views: 14421

Answers (4)

Doua Beri
Doua Beri

Reputation: 10939

If the string will always be 32 chars (probably you're having an md5 hash) then go for Char(32) You can always run this script against any table and see what mysql has to say

SELECT * FROM table PROCEDURE ANALYSE();

This will analyze your entire rows and give you some suggestions. Works well only if you have a decent number or rows in your table.

Upvotes: 0

Camden S.
Camden S.

Reputation: 2245

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?

Upvotes: 7

hkf
hkf

Reputation: 4520

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

Upvotes: 10

Brendan Long
Brendan Long

Reputation: 54242

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.

Upvotes: 5

Related Questions