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?
> 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-
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments