List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:October 9 2002 7:57pm
Subject:Re: CHAR vs. VARCHAR
View as plain text  
>From: christophe barbe <christophe@stripped>
>
>Is it then correct that VARCHAR(255) and TINYTEXT are strictly
>equivalent?

I believe they work almost the same, but there may be performance implications because
they are stored differently.

My understanding is that TINYTEXT is simply a sort of BLOB with FULLTEXT indexing
capability. BLOBs are stored separate from the main record, thus an additional
re-direction is involved, which may involve moving the disk heads.

Thus, BLOBs (and TEXT) cannot be properly indexed (although TEXT can have FULLTEXT
indeces, which are more complicated than normal indeces), and thus cannot be key fields.

I don't know this from looking at the source code or MySQL docs, just from experience with
other databases and a quick look at what is enabled via phpMyAdmin. Feel free to correct
me!

Also, I think something else written in this thread is wrong. Someone said that CHAR is
much quicker than VARCHAR when traversing records, because the VARCHAR field has to be
searched for the end of the string. AFAIK, it only has to look at the size field to
determine how to skip to the next field, which is MUCH faster than searching a string for
a terminator. This has an additional implication that VARCHAR fields are always bigger for
a given string than an appropriately sized CHAR field.

Again, corrections welcome, since this is stuff I learned from deep ponderings while
optimizing Sybase and Informix databases.

>NOTE: I understand now the "sql,query" stuff. Strange idea.

Thanks. I needed that. :-)

Thanks to the admins for adding it, too. Spam can be a pain, but due to other thoughtful
mail I receive, I've already re-financed my house, have low long distance rates, are on
first-name basis with all the hottest {appropriate adjective here} chicks, and have huge
breasts and penis, so I have no need of further such goods and services offered via this
list... :-)

-- 
: Jan Steinman -- nature photography: <http://www.Bytesmiths.com>
: Bytesmiths -- artists' services: <http://www.Bytesmiths.com/Services>
: Join the forums at <http://www.Bytesmiths.com/wiki>

Thread
CHAR vs. VARCHARchristophe barbe8 Oct
  • Re: CHAR vs. VARCHARMichael T. Babcock8 Oct
    • Re: CHAR vs. VARCHARChristophe Barbe8 Oct
  • Re: CHAR vs. VARCHARMichael T. Babcock8 Oct
  • Re: CHAR vs. VARCHARMichael T. Babcock8 Oct
  • Re: CHAR vs. VARCHARBrent Baisley8 Oct
    • Re: CHAR vs. VARCHARChristophe BarbĂ©8 Oct
    • Re: CHAR vs. VARCHARchristophe barbe8 Oct
Re: CHAR vs. VARCHARJan Steinman9 Oct