List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:October 8 2002 4:06pm
Subject:Re: CHAR vs. VARCHAR
View as plain text  
VARCHAR basically sets a maximum length for the field and only stores 
the data that is entered into it, thus saving on space. The CHAR type 
has a fixed length, so if you set CHAR(100), 100 character worth of 
space will be used regardless of what the contents are.
The only time you will gain a speed advantage is if you have no variable 
length fields in your record (varchar, text, etc.). You may notice that 
all your CHAR fields are changed to VARCHAR as soon as a variable length 
field type is added. CHAR is less efficient from a space storage point 
of view, but more efficient for searching and adding. It's faster 
because the database only has to read an offset value to get a record 
rather than reading parts until it finds the end of a record.

Also, fixed length records will minimize fragmentation since deleted 
record space can be reused for new records.

I always try to create fixed length records, sticking to CHAR types. I 
try to split out TEXT types into a separate table, although occasionally 
(rarely) it's not worth the extra coding effort.

Hope that helps a bit. I didn't go into all the +/- off each field type, 
but I think those are the biggest ones.


On Tuesday, October 8, 2002, at 09:50 AM, christophe barbe wrote:

> After reading the mysql documentation, I am not sure to get correctly
> the pros and cons of the VARCHAR type.
>
> My understanding is that it is useful when a text field
> has a length that may vary a lot.
>
> For example I am thinking using it for a description field
> where users will put nothing or a small text (for example
> "cf protocol 43") or a bigger text.
>
> Am I right in my understanding?
>
> If yes, Is it right that using VARCHAR(255) has no influence
> on the database size? I mean let said that users never use
> more than 128 chars, would have defined the field as
> VARCHAR(129) be better?
>
> What are the disadvantages of using VARCHAR instead of CHAR.
> Is it going to be considerably slower?
>
> Also, If I want to allow users to add an optional comment for
> each entry of a table, is it reasonnable to use a VARCHAR so
> that only one byte is used when no comment are added? or should
> I create another table to store the comments only when defined by
> the user?
>
> Thanks,
> Christophe
>
> --
> Christophe Barbé <christophe.barbe@stripped>
> GnuPG FingerPrint: E0F6 FADF 2A5C F072 6AF8  F67A 8F45 2F1E D72C B41E
>
> Imagination is more important than knowledge.
>    Albert Einstein, On Science
>
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
> To request this thread, e-mail <mysql-thread121615@stripped>
> To unsubscribe, e-mail <mysql-unsubscribe-
> brent=landover.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

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