Thanks for reply Micahel, Basically, my primary concern is speed (or actually, cpu loads). I don't care about space - I have a fairly small database but its going to be linked to a webserver that will be getting a lot of hits. I'm trying to squeeze as much performance out of the DB as I can so that I don't need to spend more money on hardware for the website. I started writing the whole thing using Access as the DB, and was going to move it to an SQL server when things got closer to release on the website. So the whole thing is structured using the Access ReplicationID datatype (fixed 16 byte binary data) for primary keys on almost all of my tables. It would take a fair bit of work now to go through the ISAPI and change everything to use autoincrement integer fields (and besides, there are a few other good features of using GUIDs for primary keys that I don't want to give up unless I have to).
I didn't index the columns in the table on the left side of the join because I didn't think it mattered for LEFT JOINs. I'll add an index and see what happens. However, in the meantime I ran some more tests. The original post was on MyISAM tables (I chose these because I thought they would be faster - at least that's the impression I got from the MySQL documentation). However, when I changed to InnoDB tables, times for all of the SELECTs more than halved, and the difference between the CHAR and VARCHAR columns dropped significantly (down to about 2% difference). So I guess I will just go with InnoDB tables and not worry too much about the autoconversion. Anyway, I'll do some more tests using the information you've given me (i.e. what happens for SELECTSs of less than 30% of table, index on left side of join, and speed comparison to using integers as primary keys). If anyone's interested in the results, I'll put them up on a webpage (http://au.geocities.com/m_fagan/VARCHARvsCHAR.html) in a couple of days (or whenever I have the time). --- Michael Stassen <[EMAIL PROTECTED]> wrote: > 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]
