Bigger than a char but smaller than a blob

Question

Char's are great because they are fixed size and thus make for a faster table. They are however limited to 255 characters. I want to hold 500 characters but a blob is variable length and that's not what I want.

Is there some way to have a fixed length field of 500 characters in MySQL or am I going to have to use 2 char fields?

Answer 1

I would suggest using a varchar(500). Even though varchar isn't a fixed length, the database should reserve the correct amount of space. You shouldn't notice any performance difference using varchar(500) over 2xchar(255).

Your also probably going to cause extra overheads by joining to chars together.

Answer 2

I would suggest using a varchar(500)

... if you have MySQL 5.0.3 or higher. In previous versions, VARCHAR was restricted to 255 characters.

Also, CHAR and VARCHAR do not work the same regarding trailing spaces. Be sure to read 10.4.1. The CHAR and VARCHAR Types (this is for MySQL 5.0).

Answer 3

@ Teifion - Is there any compelling reason it has to be a fixed field size?

If not, I would suggest varchar(500).

Answer 4

Char's are great because they are fixed size and thus make for a faster table.

It depends on what you're doing. Tables with fixed size rows typically are faster for update statements. On the other hand if you do a lot of querying of non indexed fields, it might be more important to keep the table small so you have to read less data from disk, varchar helps with that.

Answer 5

You're worrying too much about internal implementation details. Don't pre-optimize.

Go with VARCHAR(500)