List:General Discussion« Previous MessageNext Message »
From:Christophe BarbĂ© Date:October 8 2002 4:13pm
Subject:Re: CHAR vs. VARCHAR
View as plain text  
Thanks, your comments are very helpful, especially the info that if I
have already a not-fixed field in a table the performance cost of adding
a VARCHAR (instead of a CHAR) is 0.

NOTE for the Mailing-list Admins: There is a 1 hour (at least) between
the post and the availability of the mail in the mailing list.

Christophe 

Le mar 08/10/2002 à 12:06, Brent Baisley a écrit :
> 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