MySQL Optimize collation table fragment

When you delete a large amount of data in your library, you may find that the data file size is not reduced. This is caused by a fragment left in the data file after the delete operation. Optimize table can remove data file fragments left after deletion, reduce file size, and speed up future read and write operations. You just have to do a batch delete, or you can perform a data table optimization operation on a regular basis (such as every two months).

OPTIMIZE table works by making a temporary copy of the original table

OPTIMIZE table Syntax
OPTIMIZE [LOCAL | No_write_to_binlog] TABLE tbl_name [, Tbl_name] ...

If you have deleted a large part of the table, or if you have made many changes to a table with variable-length rows (a table with VARCHAR, blob, or text columns), you should use optimize table. The deleted records are kept in the linked list, and subsequent insert operations re-use the old record location. You can use the Optimize table to reuse unused space and defragment the data files.

In most settings, you do not need to run optimize TABLE at all. Even if you have a large number of updates to a variable-length row, you do not need to run it frequently, once a week or once a month, only for specific tables.

OPTIMIZE table works only on MyISAM, BDB, and InnoDB tables.

For the MyISAM table, OPTIMIZE table operates as follows:

1. If the table has been deleted or the row is broken down, repair the table.
2. If the index pages are not categorized, categorize them.
3. If the statistics for the table are not updated (and cannot be repaired by classifying the indexes), then the updates are made.

for the BDB table, OPTIMIZE table is currently mapped to analyze table.

for the InnoDB table, the OPTIMIZE table is mapped to the ALTER TABLE, which rebuilds the tables. The rebuild operation can update the index statistics and free unused space in the clustered index.

Use the-skip-new or-safe-mode option to start mysqld. By starting mysqld, you can make optimize table work for other table types.

Note that MySQL locks the table during the Optimize table run.

The OPTIMIZE table statement is written to the binary log unless the No_write_to_binlog keyword (or its alias local) is used. Already done so, the optimize table command for the MySQL server acts as a replication master, which, by default, is replicated to the replication subordinate server.

MySQL Optimize collation table fragment