Which is better VARCHAR(3) or CHAR(3) ?

Randell D.

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?

Thanks in advance - replies please, via the newsgroup for all to learn...

Randell D.

Jul 20 '05 #1

Bill Karwin

Randell D. wrote:

Which is better, a VARCHAR on a very small column, or using CHAR instead
and loosing out on a little disc?

Sounds like he was describing a performance improvement specific to
loading large numbers of records. There's also performance of read
queries to take into account.

I would be very surprised if the performance difference in this case
were significant, compared to performance improvements gained from other
techniques, such as increasing MySQL cache size, or deferring index
updates during data loads.

Performance issues are definitely an area to apply "penny wise, pound
foolish" principles.

Bill K.

Jul 20 '05 #2

J West

I don't care!

Sorry, that sounds a little glib.
I am from the old school where every ounce of performance and every byte/bit
of code ws an overhead and this sounds like one of those issues that would
cause debates to rage for months and occasionallly turn quite nasty. But
nowaday's it just doesn't matter.
I always tend to use varchar whatever database I am using as the performance
and space issues of hardware nowaday's make no difference worth worrying
about! This was more of a consideration when hard drives were £1 a meg
nowadays a 200 gig fast hard drive can cost less than £90 and as a
professional developer my time is worth more that £60.00 per hour (It's just
a shame I don't get to see it all) In fact my time is charged out at £300
per hour for the company I currently work for. These considerations just
take too much time to worry about!

I deal currently in mega large quantities of data and I am more interested
in gaining minuites rather than nanoseconds when dealing with data access.

Sorry if this was not the answer you were looking for but things like this
need putting into perspective

Regards

James West

"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?

Thanks in advance - replies please, via the newsgroup for all to learn...

Randell D.

Jul 20 '05 #3

Thomas Bartkus

"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

Jul 20 '05 #4

Thomas Bartkus

"Dave Farrance" <Da**********@OMiTTHiSyahooANDTHiS.co.uk> wrote in message
news:f3********************************@4ax.com...

If I want to write a quick program to solve an engineering problem that
needs a graphical plot, like drawing a few lines and curves, I tend to
use "yabasic" because it comes with Mandrake 10.x and is also available
for MS windows. It's a traditional basic with enough extras to make it a
properly structured language, and it has user-friendly graphical
plotting commands. I've tended to use it because it's similar to MS
QBasic, which I used for similar tasks in the past.

The problems start if somebody else needs to use and update my programs.
Basic is not so good, because all the commands for loop structures and
graphics are not part of the original basic language, and vary from one
implementation of basic to the next, so I can't expect anybody else to
already know the language. I guess that I should really be using a
language with a more consistent definition - like almost any other
language.

Can I have recommendations for the best "industry standard" language,
compiled or interpreted, that can be used for generating simple
graphical plots, please?

"industry standard"? That part of your request is a clunker!

Nontheless, I'm going to take a stab at practical solutions.
If we were talking the Windows world, Excel with would be a slam dunk for
what you are asking. Excels cartesion plotting capabilties are respectable,
it's math/engineering capabilities are superb, the built in VBA language is
easy and ubiquitous, AND it would be perfectly fair to call it industry
standard. One can use the Excel package for both "Simple" and exceedingly
complex plots.

However, we don't have Excel here.
Or do we?

I haven't evaluated them for (yet) for the purposes you mention but both the
spreadsheets Open Office Calc and GNumeric are supposted to be Excel clones
complete with BASIC macro languages behind them. I need exactly what you are
asking for and this is where I will be looking.

But industry standard? Good luck with that part!
Thomas Bartkus

Jul 20 '05 #5

Thomas Bartkus

"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

Jul 20 '05 #6

Thomas Bartkus

Whoops. Sorry about that.

Thoams Bartkus

Jul 20 '05 #7

Randell D.

Bill Karwin wrote:

Randell D. wrote:

Which is better, a VARCHAR on a very small column, or using CHAR
instead and loosing out on a little disc?

Sounds like he was describing a performance improvement specific to
loading large numbers of records. There's also performance of read
queries to take into account.

I would be very surprised if the performance difference in this case
were significant, compared to performance improvements gained from other
techniques, such as increasing MySQL cache size, or deferring index
updates during data loads.

Performance issues are definitely an area to apply "penny wise, pound
foolish" principles.

Bill K.

Thanks for the advice...

Randell D.

Jul 20 '05 #8

Randell D.

Thomas Bartkus wrote:

"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

Thanks for that - especially the references of the MySQL documentation
- My database is least likely to hit more than 20gbyte - at least I
don't see that ceiling for the first couple of years therefore I'll go
for the CHAR as opposed to VARCHAR option.

Cheers
Randell D.

Jul 20 '05 #9