List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 18 2008 3:20pm
Subject:Re: optimize mysql table's physical storage
View as plain text  
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.

	Dan Nelson
optimize mysql table's physical storagetech user18 Apr
  • Re: optimize mysql table's physical storageDan Nelson18 Apr