mysql varchar and text doubts, reloaded ;)

Question

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?

Answer 1

I think your assumptions there aren't entirely true and might be overly paranoid or tainted by historical concerns that are no longer valid.

Generally it's best to use VARCHAR because MySQL, and other database engines, have been optimized to handle them quite well. Historically speaking (1990s) there were significant performance differences when using tables with fixed-length rows vs. variable length but that's of little concern today.

Likewise, TEXT columns used to have all their data stored external from the primary table data. Now I believe there's a hybrid approach used, with the first N characters stored in the row and the remainder stored in the BLOB system, if necessary. It is variable length by definition, a single character does not use 65535 bytes to store.

You'll need to use LONGTEXT for much larger blocks of text, but at that point you should evaluate if you're using your database correctly. Storing, retrieving and manipulating huge objects in there can be a serious hassle.

I'd use VARCHAR(255) for anything "text" by default and extend it longer if necessary. Shortening that is generally problematic except for very narrow cases, like where you're storing an ISO country code that will, by definition be two characters.

Presuming names are "no more than 32 characters" is almost always a mistaken assumption. Leave these wide open unless you've got a very good reason to do otherwise.

You might think that things like city or place names are short, too, but you'd be wrong. The world is a strange place. Expect the unexpected.