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é <[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
