mySQL column type Q

[kycan]

Is there any real benefit from picking, say, a CHAR(20) over a VARCHAR(20) or just a TEXT field?

Please answer.

[[deleted]]

char is fixed length, char(20) always takes up 20 bytes (plus overhead),
varchar and text use only as many bytes as their content (plus overhead).
Check out the MySQL manual about column types and their definitions.

[[deleted]]

How do you explain a select on CHAR being faster than on VARCHAR?

[kycan]

Here's a question. Would it be better to have an absurdly large CHAR or VARCHAR for a column you know will be long, or would you just use TEXT? Or something else?

Thanks for the responses so far.

[[deleted]]

Don't you think the fragmentation of the records using varchar is a much bigger issue than reading those few extra bytes?

[[deleted]]

Depends on what's "better" for you.

If you want speed and the data is within the limit of what a CHAR field can store (255 characters), than CHAR would do.

If you want to save diskspace, use varchar.
And if you want to store more than 255 characters, use a text type.

check out the mysql manual about what holds what and why.

http://www.mysql.com/documentation/mysql/commented/manual.php?section=node_170

[[deleted]]

But optimize is something you don't want to do too often.