GNU Info

(mysql.info)OPTIMIZE TABLE

---

Next: ANALYZE TABLE Prev: Database Administration Up: Database Administration

---

Enter node , (file) or (file)node

`OPTIMIZE TABLE' Syntax
-----------------------

     OPTIMIZE TABLE tbl_name[,tbl_name]...

`OPTIMIZE 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 and to defragment the data file.

For the moment `OPTIMIZE TABLE' only works on *MyISAM* and `BDB'
tables. For `BDB' tables, `OPTIMIZE TABLE' is currently mapped to
`ANALYZE TABLE'. Note: ANALYZE TABLE.

You can get optimize table to work on other table types by starting
`mysqld' with `--skip-new' or `--safe-mode', but in this case `OPTIMIZE
TABLE' is just mapped to `ALTER TABLE'.

`OPTIMIZE TABLE' works the following way:
   * If the table has deleted or split rows, repair the table.

   * If the index pages are not sorted, sort them.

   * If the statistics are not up to date (and the repair couldn't be
     done by sorting the index), update them.

`OPTIMIZE TABLE' for `MyISAM' tables is equvialent of running
`myisamchk --quick --check-changed-tables --sort-index --analyze' on
the table.

Note that the table is locked during the time `OPTIMIZE TABLE' is
running!