SEO

Hi,

People said that using constant char length is a good practice as the
speed of accessing of a row is faster.

However, as the size of the table might get bigger and bigger, is this
really a good trade off?

On 30 Jun 2006 09:07:29 -0700, howachen@gmail.com wrote:
> Hi,
>
> People said that using constant char length is a good practice as the
> speed of accessing of a row is faster.
>
> However, as the size of the table might get bigger and bigger, is this
> really a good trade off?

I don't know about mysql specifically, but it's far less important than
it used to be -- varchar speed's pretty good these days. Additionally,
most engines in general don't ever shrink the varchar spaces of a
particular record, short of reorganizing a table (if supported), or
reimporting it. So, while a char table may grow quickly with inserts
(gotta hold all that unused space), a varchar table ends up growing some
even with just updates and tends (over time) to approach the size of the
char table anyway.

So, I guess I wouldn't really worry about using char instead of varchar
unless I had a small table, with well-chosen column specifications (not
much wild variation in the widths of data in the char columns), where
all the records were expected to change rapidly.

--
Cunningham's Second Law:
It's always more complex than you expect, even when you take
Cunningham's Second Law into account.

howachen@gmail.com wrote:
> People said that using constant char length is a good practice as the
> speed of accessing of a row is faster.

Here's the page that describes the advantages of using fixed-length CHAR
columns over dynamic-length VARCHAR columns.

http://dev.mysql.com/doc/refman/5.0/...ic-format.html

This applies only to MyISAM tables. Note also that _all_ the columns in
a table must be fixed-length datatypes, or else you don't get the
advantage. That is, if the table contains at least one VARCHAR, BLOB,
or TEXT column, then that whole table is stored as a dynamic table.

That doc page does say that fixed-length rows have some performance
advantage, but it doesn't quantify it. So it could be a trivial advantage.

I usually use the datatype that most fits the application, then worry
about optimizations if I determine that it's causing a bottleneck.
Other improvements like indexes and caches are almost always more
important than the performance difference between CHAR and VARCHAR.

Regards,
Bill K.

Bill Karwin 寫道：

> howachen@gmail.com wrote:
> > People said that using constant char length is a good practice as the
> > speed of accessing of a row is faster.
>
> Here's the page that describes the advantages of using fixed-length CHAR
> columns over dynamic-length VARCHAR columns.
>
> http://dev.mysql.com/doc/refman/5.0/...ic-format.html
>
> This applies only to MyISAM tables. Note also that _all_ the columns in
> a table must be fixed-length datatypes, or else you don't get the
> advantage. That is, if the table contains at least one VARCHAR, BLOB,
> or TEXT column, then that whole table is stored as a dynamic table.
>
> That doc page does say that fixed-length rows have some performance
> advantage, but it doesn't quantify it. So it could be a trivial advantage.
>
> I usually use the datatype that most fits the application, then worry
> about optimizations if I determine that it's causing a bottleneck.
> Other improvements like indexes and caches are almost always more
> important than the performance difference between CHAR and VARCHAR.
>
> Regards,
> Bill K.

thanks.

Bill Karwin 寫道：

> howachen@gmail.com wrote:
> > People said that using constant char length is a good practice as the
> > speed of accessing of a row is faster.
>
> Here's the page that describes the advantages of using fixed-length CHAR
> columns over dynamic-length VARCHAR columns.
>
> http://dev.mysql.com/doc/refman/5.0/...ic-format.html
>
> This applies only to MyISAM tables. Note also that _all_ the columns in
> a table must be fixed-length datatypes, or else you don't get the
> advantage. That is, if the table contains at least one VARCHAR, BLOB,
> or TEXT column, then that whole table is stored as a dynamic table.
>
> That doc page does say that fixed-length rows have some performance
> advantage, but it doesn't quantify it. So it could be a trivial advantage.
>
> I usually use the datatype that most fits the application, then worry
> about optimizations if I determine that it's causing a bottleneck.
> Other improvements like indexes and caches are almost always more
> important than the performance difference between CHAR and VARCHAR.
>
> Regards,
> Bill K.

it that mean this problem does not exist if i use InnoDB, rather then
MyISAM ?

thanks...

howachen@gmail.com wrote:
> Bill Karwin wrote
>>
>> Here's the page that describes the advantages of using fixed-length CHAR
>> columns over dynamic-length VARCHAR columns.
>>
>> http://dev.mysql.com/doc/refman/5.0/...ic-format.html
>>
>> That doc page does say that fixed-length rows have some performance
>> advantage, but it doesn't quantify it. So it could be a trivial advantage

Advantages from fixed length records depend heavily on the table size
and usage pattern. In most cases the extra lookup of the record
position (compared to the simple calculation record# * recordlen) is
*not* a problem. A more serious problem is table space fragmentation.
It happens if you modify and/or delete in a table with variable length
records. MyISAM doesn't try very hard to find a place for new/modified
records. So a busy table may end with lots of holes in the data file.
Fixed length records completely eliminate the fragmentation problem.
Records can be modified in place and a "hole" has always space for one
or more complete records.

InnoDB does not have the (extra) fragmentation problem, because it
stores records of variable length in a single table space anyway.
Therefore InnoDB always has to cope with fragmentation.

>> I usually use the datatype that most fits the application, then worry
>> about optimizations if I determine that it's causing a bottleneck.
>> Other improvements like indexes and caches are almost always more
>> important than the performance difference between CHAR and VARCHAR.

Exactly.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke wrote:
> howachen@gmail.com wrote:
>
>>Bill Karwin wrote
>>
>>>Here's the page that describes the advantages of using fixed-length CHAR
>>>columns over dynamic-length VARCHAR columns.
>>>
>>>http://dev.mysql.com/doc/refman/5.0/...ic-format.html
>>>
>>>That doc page does say that fixed-length rows have some performance
>>>advantage, but it doesn't quantify it. So it could be a trivial advantage
>
>
> Advantages from fixed length records depend heavily on the table size
> and usage pattern. In most cases the extra lookup of the record
> position (compared to the simple calculation record# * recordlen) is
> *not* a problem. A more serious problem is table space fragmentation.
> It happens if you modify and/or delete in a table with variable length
> records. MyISAM doesn't try very hard to find a place for new/modified
> records. So a busy table may end with lots of holes in the data file.
> Fixed length records completely eliminate the fragmentation problem.
> Records can be modified in place and a "hole" has always space for one
> or more complete records.
>
> InnoDB does not have the (extra) fragmentation problem, because it
> stores records of variable length in a single table space anyway.
> Therefore InnoDB always has to cope with fragmentation.
>
>
>>>I usually use the datatype that most fits the application, then worry
>>>about optimizations if I determine that it's causing a bottleneck.
>>>Other improvements like indexes and caches are almost always more
>>>important than the performance difference between CHAR and VARCHAR.
>
>
> Exactly.
>
>
> XL
> --
> Axel Schwenke, Senior Software Developer, MySQL AB
>
> Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/

It can, however, have serious effects when MySQL needs to do a table scan.

If all the fields are fixed length, the table scan is very easy - just add the
size of a record to the current pointer to get the next record. However, if
there are variable length fields in the record, MySQL needs to calculate the
size of each column in the current row to get to the next row.

But InnoDB does have fragmentation problems, also. Yes, it stores variable
length records in a separate table space. But the fragmentation still occurs,
and eventually can cause performance problems.

More advanced databases have REORG commands. MySQL doesn't have one, but you
can do a backup/delete/restore to get the same effect.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle <jstucklex@attglobal.net> wrote:
> Axel Schwenke wrote:

[MyISAM fragmentation]

>> InnoDB does not have the (extra) fragmentation problem, because it
>> stores records of variable length in a single table space anyway.
>> Therefore InnoDB always has to cope with fragmentation.

> It can, however, have serious effects when MySQL needs to do a table scan.

Not *so* serious. MyISAM engine reads hunks of read_buffer_size anyway.
So again the difference between fixed and variable length records is
just another lookup for the record length. Details can be found here:

http://dev.mysql.com/doc/internals/e...roduction.html

> But InnoDB does have fragmentation problems, also. Yes, it stores variable
> length records in a separate table space. But the fragmentation still occurs,
> and eventually can cause performance problems.

In fact fragmentation is inevitable in InnoDB - because there always
are records of different lenght. And InnoDB stores data on pages.
However, InnoDB can avoid fragmentation in some cases where MyISAM
can't. Because InnoDB was designed with fragmentation in mind.

> More advanced databases have REORG commands. MySQL doesn't have one, but you
> can do a backup/delete/restore to get the same effect.

For InnoDB you can use ALTER TABLE ... ENGINE=InnoDB. Of course this
is documented in the manual:

http://dev.mysql.com/doc/refman/5.0/...agmenting.html


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke wrote:
> Jerry Stuckle <jstucklex@attglobal.net> wrote:
>
>>Axel Schwenke wrote:
>
>
> [MyISAM fragmentation]
>
>
>>>InnoDB does not have the (extra) fragmentation problem, because it
>>>stores records of variable length in a single table space anyway.
>>>Therefore InnoDB always has to cope with fragmentation.
>
>
>>It can, however, have serious effects when MySQL needs to do a table scan.
>
>
> Not *so* serious. MyISAM engine reads hunks of read_buffer_size anyway.
> So again the difference between fixed and variable length records is
> just another lookup for the record length. Details can be found here:
>
> http://dev.mysql.com/doc/internals/e...roduction.html
>

Yes, it does. But where it starts can matter. A large row with several
variable length fields may require multiple additional buffers being read.
Additionally, just having to read the data and calculate the beginning of the
next column in that row takes time.

>
>>But InnoDB does have fragmentation problems, also. Yes, it stores variable
>>length records in a separate table space. But the fragmentation still occurs,
>>and eventually can cause performance problems.
>
>
> In fact fragmentation is inevitable in InnoDB - because there always
> are records of different lenght. And InnoDB stores data on pages.
> However, InnoDB can avoid fragmentation in some cases where MyISAM
> can't. Because InnoDB was designed with fragmentation in mind.
>

Yes, InnoDB was designed with fragmentation in mind. And it does a decent job
at limiting fragmentation. But that in itself adds additionally overhead. And
fragmentation does exist.

>
>>More advanced databases have REORG commands. MySQL doesn't have one, but you
>>can do a backup/delete/restore to get the same effect.
>
>
> For InnoDB you can use ALTER TABLE ... ENGINE=InnoDB. Of course this
> is documented in the manual:
>
> http://dev.mysql.com/doc/refman/5.0/...agmenting.html
>

Yes, you can change the table to be managed by InnoDB. But that's not at all
the same as reorganizing the table.

All of the more advanced engines - Oracle, SQL Server, DB2, etc. also have
fragmentation-minimizing algorithms. Most are more advanced that InnoDB's -
because they are more tightly integrated into the database instead of being a
separate engine. And all of them have a need for a REORG command to defragment
tables.

And all of them agree that working with fixed length columns is more efficient
than variable length columns for the above reasons.

>
> XL
> --
> Axel Schwenke, Senior Software Developer, MySQL AB
>
> Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/

Axel,


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:RZadnUJrOqkPgDTZnZ2dnUVZ_omdnZ2d@comcast.com. ..
> Axel Schwenke wrote:
>> Jerry Stuckle <jstucklex@attglobal.net> wrote:
>>
>>>Axel Schwenke wrote:
>>
>>
>> [MyISAM fragmentation]
>>
<snip>
>>
>>>More advanced databases have REORG commands. MySQL doesn't have one, but
>>>you
>>>can do a backup/delete/restore to get the same effect.

MySQL has the OPTIMIZE TABLE command for MyISAM tables which will reclaim
the unused space and to defragment the data file. Refer to
http://dev.mysql.com/doc/refman/5.0/...ize-table.html

--
Tony Marston

http://www.tonymarston.net
http://www.radicore.org