>>>>> "Paul" == Paul DuBois <paul@stripped> writes:
Paul> I'm curious about something.
Paul> VARCHAR(255) BINARY and TINYBLOB both have a maximum length of 255
Paul> characters and are case sensitive.
Paul> Similarly, VARCHAR(255) and TINYTEXT both have a maximum length of 255
Paul> characters and are case insensitive.
Paul> However, BLOB/TEXT columns cannot have NULL values, they cannot be
Paul> indexed, and they cannot have default values. Given the choices above,
Paul> when would you ever *not* choose VARCHAR(255) and VARCHAR(255) BINARY
Paul> over TINYBLOB/TINYTEXT?
BLOB columns can have NULL values.
Paul> There isn't any trailing space removal for BLOB/TEXT values when you
Paul> store them. If you need that, then you might choose TINYBLOB/TINYTEXT,
Paul> I suppose.
Paul> Are there any other reasons?
The internal handling of BLOB/TEXT are totally different compared to
CHAR/VARCHAR. A CHAR/VARCHAR column takes a little more memory in
some context but should be generally be faster. In some context when
you have MANY varchar columns in the same table you may be better of
with using TINYBLOB/TINYTEXT.
The difference is as follows:
For each table in use, MySQL allocates memory for 4 rows. For each of
these rows CHAR(X)/VARCHAR(X) column takes up the X characters.
A TEXT/BLOB on the other hand is represented by a 8 byte pointer + a
1-4 byte length (depending on the BLOB/TEXT type). The BLOB/TEXT is
allocated dynamicly on use. This will use less memory, but in some
cases it may fragment your memory in the long run.
In most cases I would recommend to use CHAR/VARCHAR insted of
*************** Warning commercial signature follows **********
If you like TCX's concept of a 'mostly free' database and free
advice, you should at least CONSIDER supporting us that we can
afford to keep this service up. http://www.mysql.com/