Re: Automatic conversion from `char` TO `varchar`

[Michael Stassen]

As I understand it, fixed-length *rows* are faster than variable-length 
rows, as mysql knows exactly where each fixed-length row starts in the 
file.  Once your table has any variable-length column, however, you have 
variable-length rows.  In that case, the smaller the rows the better, 
speedwise.  So, once your table has a variable-length column, mysql 
changes the CHARs to VARCHARS to save space and improve efficiency.

See <http://www.mysql.com/doc/en/Silent_column_changes.html> for more.

...

After writing the above, I went and looked at your test results. 
Interesting.  I imagine you've already read about silent column changes. 
 I have a couple questions about your test.

- I notice that in tables test2 and test4, the CHAR and VARCHAR columns 
are indexed, but they are not indexed in tables test1 and test3.  This 
means that each of your LEFT JOINS has an index on the right, but not on 
the left.  Was that on purpose?  Usually, you'd want an index on both 
sides of the join condition.  I don't really expect that to make any 
difference here, though, as you are selecting more than 30% of the rows 
(all of them, in fact).

- Have you tried the equivalent test joining on the int columns?  That 
would control for any difference between fixed/variable length rows, as 
oppposed to the relative efficiency of CHAR vs. VARCHAR.

Michael

Matt Fagan wrote:

I'm having the same problem. I did a performance test, and
CHAR columns are significantly faster than VARCHAR (at
least on my platform - MySQL 4.1.1a on Win32). I setup a
webpage with my sample code (VB) so that you can run the
test yourself:
http://au.geocities.com/m_fagan/VARCHARvsCHAR.html

The results were that table join on CHAR-CHAR was about 15%
faster than join on VARCHAR-VARCHAR. Does anyone know how
to stop the auto-conversion of CHAR to VARCHAR ?
----- Original Message ----- 
From: "Hassan Shaikh" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Saturday, January 10, 2004 7:06 AM
Subject: Automatic conversion from `char` TO `varchar`



Hi,

It's really strange but when I execute the following
statement, all my
char(10) columns turn into varchar(10). My other tables are
ok and I've
tried create dummy table also. Problem seems to be
associated with this
table only.
...

Matt Fagan

http://greetings.yahoo.com.au - Yahoo! Greetings
Send your love online with Yahoo! Greetings - FREE!


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