Table Maintenance Statements During the prepare phase, metadata is updated and an intermediate table is created. During the commit phase, table metadata changes are committed. OPTIMIZE TABLE  rebuilds the table using the table copy method under the following conditions: OPTIMIZE TABLE  using online DDL is not supported for  InnoDB  tables that contain  FULLTEXT  indexes. The table copy method is used instead. InnoDB  stores data using a page-allocation method and does not suffer from fragmentation in the same way that legacy storage engines (such as  MyISAM ) do. When considering whether or not to run optimize, consider the workload of transactions that your server is expected to process: For  MyISAM  tables,  OPTIMIZE TABLE  works as follows: OPTIMIZE TABLE  is performed online for regular and partitioned  InnoDB  tables. Otherwise, MySQL locks the table during the time  OPTIMIZE TABLE  is running. OPTIMIZE TABLE  does not sort R-tree indexes, such as spatial indexes on  POINT  columns. (Bug #23578) REPAIR TABLE  repairs a possibly corrupted table, for certain storage engines only. This statement requires  SELECT  and  INSERT  privileges for the table. Although normally you should never have to run  REPAIR TABLE , if disaster strikes, this statement is very likely to get back all your data from a  MyISAM  table. If your tables become corrupted often, try to find the 2897 REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE  tbl_name  [,  tbl_name ] ... [QUICK] [EXTENDED] [USE_FRM] 13.7.3.5 REPAIR TABLE Statement Other Considerations 3. If the table’s statistics are not up to date (and the repair could not be accomplished by sorting the index), update them. 2. If the index pages are not sorted, sort them. 1. If the table has deleted or split rows, repair the table. •High-concurrency workloads might leave gaps in indexes over time, as  InnoDB  retains multiple versions of the same data due through its MVCC mechanism. See Section 15.3, “InnoDB Multi-Versioning” . •Updates to rows usually rewrite the data within the same page, depending on the data type and row format, when sufficient space is available. See Section 15.9.1.5, “How Compression Works for InnoDB Tables”  and Section 15.10, “InnoDB Row Formats” . •Delete operations might leave gaps that leave pages less filled than desired, which could make it worthwhile to optimize the table. •Some level of fragmentation is expected.  InnoDB  only fills pages 93% full, to leave room for updates without having to split pages. •When the  old_alter_table  system variable is enabled. •When the server is started with the  --skip-new  option. MyISAM Details