content_image/662382.jpg
For NDB Cluster tables, OPTIMIZE TABLE  can be interrupted by (for example) killing the SQL thread performing the OPTIMIZE  operation. 

By default, OPTIMIZE TABLE  does not  work for tables created using any other storage engine and returns a result indicating this lack of support. You can make OPTIMIZE TABLE  work for other storage engines by starting mysqld  with the --skip-new  option. In this case, OPTIMIZE TABLE  is just mapped to ALTER TABLE . 

This statement does not work with views. 

OPTIMIZE TABLE  is supported for partitioned tables. For information about using this statement with partitioned tables and table partitions, see Section 24.3.4, “Maintenance of Partitions” . 

By default, the server writes OPTIMIZE TABLE  statements to the binary log so that they replicate to replicas. To suppress logging, specify the optional NO_WRITE_TO_BINLOG  keyword or its alias LOCAL . 

## OPTIMIZE TABLE Output 

•OPTIMIZE TABLE Output 

•InnoDB Details 

•MyISAM Details 

•Other Considerations 

OPTIMIZE TABLE  returns a result set with the columns shown in the following table. 

<img src='content_image/662382.jpg'>

OPTIMIZE TABLE  table catches and throws any errors that occur while copying table statistics from the old file to the newly created file. For example. if the user ID of the owner of the .MYD  or .MYI  file is different from the user ID of the mysqld  process, OPTIMIZE TABLE  generates a "cannot change ownership of the file" error unless mysqld  is started by the root  user. 

## InnoDB Details 

For InnoDB  tables, OPTIMIZE TABLE  is mapped to ALTER TABLE ... FORCE , which rebuilds the table to update index statistics and free unused space in the clustered index. This is displayed in the output of OPTIMIZE TABLE  when you run it on an InnoDB  table, as shown here: 

mysql> OPTIMIZE TABLE foo; +----------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+----------+----------+-------------------------------------------------------------------+ | test.foo | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.foo | optimize | status | OK | +----------+----------+----------+-------------------------------------------------------------------+ 

OPTIMIZE TABLE  uses online DDL for regular and partitioned InnoDB  tables, which reduces downtime for concurrent DML operations. The table rebuild triggered by OPTIMIZE TABLE  is completed in place. An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation. 

