Re: varchar < 4 = char < 4? Why?

[Michael Stassen]

In MySQL, the only practical difference between a CHAR and a VARCHAR is how 
it is stored.  CHARs are fixed-length, so they are fast but can waste space. 
 VARCHARs are variable length, so they are slow but (usually) save space. 
That's the key here -- the only advantage to a varchar is the space savings. 
 But, each varchar takes one more byte than the length of the string in 
order to store the length.  When your column is less than 4 characters, that 
extra byte makes a significant dent in your space savings, thus removing the 
sole advantage of varchar over char.  For example, it takes 2 bytes to store 
a CHAR(2) and 1 to 3 bytes to store a VARCHAR(2).  MySQL is trying to do you 
a favor by changing your column.  Whether you agree it's done you a favor or 
not seems to be a matter of opinion.

You should definitely read the "Silent Column Specification Changes" 
<http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html> section of 
the manual.

Michael

Stormblade wrote:

This has been puzzling me. At first I thought it was something that Navicat
was doing but I also tried in EMS MySQL and it does the same.
If I set the type of a field to varchar and set the length to anything less
than 4 it will get converted to a char type of the same length. Now I know
that char is faster. I read it can be up to 50% faster but I am curious why
I am prevented from having a varchar of length less than 4?
Is it prohibitively expensive to do this in MySQL? Is it such a bad idea
that they simply don't allow you to do it? Inquiring minds want to know.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]