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
==================