"Randell D." <re******************************@fiprojects.moc > wrote in
message news:2_jDd.738061$%k.113808@pd7tw2no...
Folks,
I once read an article in Linux Format whereby a technical writer had
made performance recommendations on a LAMP environment. One of the
points raised was for small columns in a database, that one is perhaps
better off to trade a small waste of space for a gain on performance -
The recommendation said that when you use VARCHAR(3) that MySQL searches
for disk space to take a record that will accept a variable length
value up to three characters (he didn't use those words - these were my
interpretations). He suggested that by using CHAR(3) - the server would
spend less time hunting down where to insert the new record... I have
problems explaining this but I'm wondering if someone with a greater dba
background then mine could help and answer...
Which is better, a VARCHAR on a very small column, or using CHAR instead
and loosing out on a little disc?
That really is a design decision for you to make.
The MySQL documentation mentions the very performance/storage space tradeoff
you noted.
If you eliminate *all* variable length data types (VarChar, Blob, Text) from
a table, then mysql can call the Row_Format "static" for this table -
meaning each record is fixed in size. The documentation claims both indexed
and non-indexed lookups will be faster if the Row_format is static.
It (the documentation) also claims that the static table structure is
somewhat more robust, less prone to corruption, and easier to repair if
problems do occur. Note that if even a single variable length field in a
table, the Row_format becomes dynamic and you lose these presumed benefits.
In my shop, we have new equipment and disk space is both plentiful and
cheap. We also deal with a rapidly increasing server load and always favor
saving cpu cycles over storage space. We tend to avoid variable length
fields like VarChar. Although - had we reason to conserve storage space, we
wouldn't hesitate to use VarChar!
IOW - Your own mileage/situation may vary!
Thomas Bartkus