OPTIMIZE TABLE tbl_name
OPTIMZE TABLE
should be used if you have deleted a large part of a
table or if you have made many changes to a table with variable-length rows
(tables that have VARCHAR
, BLOB
or TEXT
columns).
Deleted records are maintained in a linked list and subsequent INSERT
operations reuse old record positions. You can use OPTIMIZE TABLE
to
reclaim the unused space.
OPTIMIZE TABLE
works by making a temporary copy of the original
table. The old table is copied to the new table (without the unused rows),
then the original table is deleted and the new one is renamed. This is done
in such a way that all updates are automatically redirected to the new table
without any failed updates. While OPTIMIZE TABLE
is executing, the
original table is readable by other clients. Updates and writes to the table
are stalled until the new table is ready.