I had a doubt with mysql. I have fields that I can easily restrict to some chars, like surname, and others like a mini log with an event particular that I think is no longer 2 than pages (or fewer characters too). I know it's a fairly common question, I see many links about that, but I can't see well what's the difference and the right way.
Here are the conclusions and doubts I have, please correct me if I'm wrong:
-varchar is an variable type, it doesn't matters if I set varchar(10) or varchar(65535), although not the same 65536, which passes to medium text.
-varchar with one or other parameters is the same, only takes necessary space, it's only a restriction to save and a restriction to representation.
-if I do the restriction in the side of my application it's exactly the same and more flexible.
-text is of fixed length, if I set text it will be occupy 65535, both I use it or not.
varchar
-it has an overloaded of having to find first what's the length of field.
-it has a cost of one byte more where the length is saved.
-it is save inline, that means in the table.
-it has better performance, always.
text
-it is stored outside the table, there is a pointer to my text, is that true?
-it have a fixed number of characters.
-the lenght that is setted both in varchar and text is in characters, not in bytes (previously versions was different)
Conclution:
-For these reasons it will be better always use a varchar, there is only one case that's not true, wich is in case of I know exactly the numbers of characters.
All the other cases, which are the most commons, will be better to use a longest varchar, therefore it is to names or surnames (although I can restrict it to text(35), but where I don't know if the name is "Tom" or "Federico Guillermo Rodrigo Cayetano"), as for a field matter or desctription (more variable), or for a text more bigger like a mini log, who can vary from 0 to 3 pages of characters (that's in my case, but we can take it to an extreme like 0 to 16 pages of text, around 65535 characters).
This is right especially if we can not know future requisites, like a longest description, if we restrict the table space, a change of requirements can be a hard problem.
Finally, like a bonus :), What happens with a text more than 65535 characters but who can vary?, it's reasonable to set it to medium text, or longtext if it can vary?