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/