Database Administration Statements

MySQL 5.5 Reference Manual/ SQL Statement syntax/database Administration statements/table Maintenance State ments

Optimize table

Mysql>? Optimize table
Name: ' OPTIMIZE TABLE '
Description:
Syntax:
OPTIMIZE [No_write_to_binlog | LOCAL] TABLE tbl_name [, Tbl_name] ...

No_write_to_binlog Suppress SQL statements are written to BINLOG

reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O Efficien CY when accessing the table. The exact changes made to each table depend in the storage engine used by that table.
Reorganize the physical storage of table data and related index data, reduce storage space, improve I/O efficiency when accessing tables, and the exact change to each table depends on the storage engine used by the table.

Use OPTIMIZE table in these cases, depending on the type of table:
Optimizing tables in these scenarios depends on the table type:
O After doing substantial inserts, update, or delete operations on a InnoDB table that have its own. ibd file because it wa s created with the innodb_file_per_table option enabled. The table and indexes is reorganized, and disk space can be reclaimed for use by the operating
System.
o After a large number of INSERT, UPDATE, or delete operations have been performed on a InnoDB table with its own. ibd file (enabled innodb_file_per_table option on creation)
By executing optimize table, the table will be re-organized and disk space can be reused by the operating system. (Clustered index)

o After deleting a large part of a MyISAM or ARCHIVE table, or making many changes to a MyISAM or ARCHIVE table with Varia Ble-length rows (tables that has VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows is maintained in a linked list and subsequent inserts operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes-a table, this statement could also improve performance of statements that use the table, sometime s significantly.
o Many changes have been made to delete a large portion of a MyISAM or archive table, or to a MYISAM archive table containing a variable-length row (a table containing a varchar VARBINARY BLOB text column),
The deleted rows are maintained by a linked list, and the subsequent insert operation re-uses the old row position. You can use optimize TABLE to reclaim unused space and defragment data files.
After a lot of changes, this statement can also significantly improve the performance of statements that use this table.
This statement requires SELECT and inserts privileges for the table.
This statement requires that you have select and Insert permissions on the table.

OPTIMIZE TABLE is also supported for partitioned tables. For information about using the statement with partitioned tables and table partitions, see
Http://dev.mysql.com/doc/refman/5.5/en/partitioning-maintenance.html.

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

OPTIMIZE table also supports partitioned tables.

Database Administration Statements