From: Dan Nelson
Date: April 18 2008 3:20pm
Subject: Re: optimize mysql table's physical storage
List-Archive: http://lists.mysql.com/mysql/212382
Message-Id: <20080418152029.GC57251@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Apr 18), tech user said:
> My mysql table has been created for long time, it increases day by
> day, and become huge. Right now a full scan to the table for the
> first time is very slow. So I was thinking to optimize it. This table
> is stored in many non-sequential disk fragments I think. I want to
> make this table to be stored in disk with the sequential
> fragments.That will increase the scan speed. Is there any tool to do
> it? Thanks in advance.

If you are solely concerned with filesystem fragmentation, shut MySQL
down, copy your .MYI and .MYD (or .ibd if you're using innodb
file-per-table) files to a temporary directory, delete the originals,
and move your new files into their place.  Or if you're running
Windows, simply run its disk defragmenter :)

If you have deleted/inserted many rows in your table and are using a
variable-length table (pretty much any table with VARCHARs), then you
may also have internal table fragmentation.  The OPTIMIZE TABLE command
will compact your file by copying the row data to a new temporary
table, rebuilding the indexes, and deleting the original table.

http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html

-- 
	Dan Nelson
	dnelson@stripped