Very newbie mysql question

varchar or text column: advantages/disadvantages?

[louponne]

Hello,

I'm a very newbie to mysql. My tables hold data that is usually limited in length. As I understand it, if a field is definitely going to be limited to, say, 50 characters, I should use varchar, whereas if it will be unlimited, or at least over 200 chars or so, I should use a text field?

Is this right? Am I missing something?

[ukgimp]

I use Text for fields over 200 characters

Some more info
mysql field types [scibit.com]

[seindal]

Hi,

If you know for certain that your field is shorter than 255 you can use a varchar field. If you put something too long (longer than the declared length of the field) into a varchar field, it is simply truncated.

You only need text fields if your field can be longer than 255 characters.

If this field is the only varchar in a table, you can use a char field to speed things up. It has a fixed length, so the whole record will have fixed length, giving MySQL a possibility to optimise accesses. If you have just one variable length field in a table, all other char fields are silently converted to varchar.

René

[louponne]

Many thanks, friends, for your quick answers! :-)

[lorax]

You can always check field length before submitting anything to the db and compare it with what is being submitted.

[php.net...]

[louponne]

ok, so I have a table that can definitely be optimized. I'll be wanting to change several TEXT fields to VARCHAR. Can I do that "on the fly" on my web server without losing any data?

[sun818]

Perhaps there is another way, but we always rename the current table, build a new table with the modified field types, then insert the data from the renamed table to the new one. As long as there is no binary data in the rename table, using this method has not been an issue. You would also need to re-run any grants or synonyms you performed for that specific table.

[seindal]

To louponne: a text field takes two bytes plus the content; a varchar takes one byte plus the content. You only save one byte by changing. In mysql all variable length fields are basically pointers to a separate storage space where the variable lenght data are stored, so there is a performace penalty to using variable length fields, be it varchar or text. The base record is fixed length, but all variable length data has to be fetched elsewhere. My guess is the penalty is the same for varchar and text.

So by changing from text to varchar you save one byte per record but no time. If you want to save time, you have to change all your fields to fixed length char fields. That'll cost you some space.

You can change you table layout with the ALTER TABLE command in MySQL. See
[mysql.com...]

You'll need a command like (field is a text field):

ALTER TABLE tablename CHANGE field field VARCHAR(size) ...;

The first "field" is the name of field you what to change and the second "field" is the new name. They can be the same.

René.

[louponne]

Thanks again - everything is now crystal clear!