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é <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> > To unsubscribe, e-mail <mysql-unsubscribe- > [EMAIL PROTECTED]> > 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 --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
