Data Types in MySQL

[tharden3]

I want to add a "product description" next to my products in a table. It might be as much as 150 characters for this. Do I make this column the varchar data type?

[Dormilich]

that seems to be sensible..... although 150 characters is not very much.

[Markus]

Yup, varchar(150).

[Atli]

Note that the VARCHAR type is a "variable-length" type, so if you define the column as VARCHAR(150), even if you only provide 50 characters, the field will only occupy the space required to store these 50 characters.

Because of this, there is little reason (except from a design perspective) to define it with anything less than 255 characters. Unless, of course, you have a very good reason not to allow more than a set number.

Also note, that using variable-length types in a table (and those include Text and Blob fields as well) may slow the query speeds on that table.
(MySQL will have to calculate the length of each row, whereas it can calculate a *static* value for the entire table if there isn't a variable-length type.)

So if your HDD space vs. CPU/RAM ratio is greatly in favor of the HDD side, defining a field CHAR(150) rather than VARCHAR(150) may be a better choice.

... Just something to think on :]

[tharden3]

So if your HDD space vs. CPU/RAM ratio is greatly in favor of the HDD side, defining a field CHAR(150) rather than VARCHAR(150) may be a better choice.

... Just something to think on :]

So varchar() will vacate memory for me, but sacrifices speed, while char() is speedier, but takes up more memory if the space is not utilized?

[Atli]

Thats the general idea, yes.

But note that if you already have a variable-length column in your table, the speed boost CHAR would provide is already lost. That boost is dependent on all the fields being fixed-length.

[tharden3]

Thats the general idea, yes.

But note that if you already have a variable-length column in your table, the speed boost CHAR would provide is already lost. That boost is dependent on all the fields being fixed-length.

oh, ok. Well thanks for the tips, that really helps.