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.

[Bill Karwin]

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

Randell D. wrote:[color=blue]
> Which is better, a VARCHAR on a very small column, or using CHAR instead
> and loosing out on a little disc?[/color]

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.

[J West]

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

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." <reply.via.news .group.only.tha nks@fiprojects. moc> wrote in
message news:2_jDd.7380 61$%k.113808@pd 7tw2no...[color=blue]
>
> 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.[/color]

[Thomas Bartkus]

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

"Randell D." <reply.via.news .group.only.tha nks@fiprojects. moc> wrote in
message news:2_jDd.7380 61$%k.113808@pd 7tw2no...[color=blue]
>
> 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?[/color]

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

[Thomas Bartkus]

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

"Dave Farrance" <DaveFarrance@O MiTTHiSyahooAND THiS.co.uk> wrote in message
news:f3dtt05ai8 f8j69g09tkfqslm 08u7rorcd@4ax.c om...[color=blue]
> 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?[/color]

"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

[Thomas Bartkus]

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

"Randell D." <reply.via.news .group.only.tha nks@fiprojects. moc> wrote in
message news:2_jDd.7380 61$%k.113808@pd 7tw2no...[color=blue]
>
> 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?[/color]

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

[Thomas Bartkus]

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

Whoops. Sorry about that.

Thoams Bartkus[color=blue]
>[/color]

[Randell D.]

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

Bill Karwin wrote:
[color=blue]
> Randell D. wrote:
>[color=green]
>> Which is better, a VARCHAR on a very small column, or using CHAR
>> instead and loosing out on a little disc?[/color]
>
>
> 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.[/color]


Thanks for the advice...

Randell D.

[Randell D.]

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

Thomas Bartkus wrote:
[color=blue]
> "Randell D." <reply.via.news .group.only.tha nks@fiprojects. moc> wrote in
> message news:2_jDd.7380 61$%k.113808@pd 7tw2no...
>[color=green]
>>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
>>interpretatio ns). 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?[/color]
>
>
> 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
>
>
>[/color]

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.