I have been following the CHAR vs VARCHAR war for a few days now, and find
the whole thing kind of funny, but worth comment.
-----Original Message-----
From: Kris Dahl <krislists@stripped>
To: jan.dvorak@stripped <jan.dvorak@stripped>
Cc: mysql@stripped <mysql@stripped>
Date: Monday, August 21, 2000 6:52 PM
Subject: Re: CHAR vs VARCHAR
>
>> CHARs can be faster than VARCHARs if the following conditions are met:
>> 1. You didn't do OPTIMIZE TABLE often enough;
>> 2. The table can fit in the O/S buffer cache anyway.
>> If the table (plus a substantial part of its index)
>> doesn't fit in the core, disk speed becomes the hurdle
>> and then, it's pretty much obvious that the less data you have
>> to pass through the core, the sooner a query is finished.
>
>I have found that even immediately after a optimize, on smaller tables at
>least (I think I tested it on a 30k row table) converting from
VARCHAR->CHAR
>was still significantly faster. Your millage will obviously vary depending
>on the application.
If you *really* need the speed advantage of using CHAR or the space
advantage of the VARCHAR then the server wasn't sized properly in the first
place. Fields should be defined as to what they will contain, a field of
fixed size values, like Licence plate numbers, should be defined as a CHAR;
a field of variable length values, like names, should be defined as VARCHAR.
This assists with self documenting the tables, so that the DBA can look at
the definition, and get an idea of what is going on. The same idea goes for
the Enum, if an enumerated field is the best bet, then use it.
You never know what the future will bring, new database technologies, may
eliminate the advantage, your carefully mangled database has, or you may get
a $50,000 budget for a new server, so it doesn't matter anymore.
Paul
