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.
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