List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 27 1999 10:44pm
Subject:Why use TINYTEXT or TINYBLOB?
View as plain text  
Hi!

>>>>> "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
TINYBLOB/TINYTEXT.

Regards,
Monty

*************** 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/

Thread
Why use TINYTEXT or TINYBLOB?Paul DuBois23 Jun
  • Why use TINYTEXT or TINYBLOB?Michael Widenius28 Jun