Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 37.9Mb
PDF (RPM) - 37.3Mb
HTML Download (TGZ) - 10.3Mb
HTML Download (Zip) - 10.3Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 216.7Kb
Man Pages (Zip) - 329.5Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual OPTIMIZE TABLE Syntax

    TABLE tbl_name [, tbl_name] ...

OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table.

Use OPTIMIZE TABLE in these cases, depending on the type of table:

  • After doing substantial insert, update, or delete operations on an InnoDB table that has its own .ibd file because it was created with the innodb_file_per_table option enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system.

  • After doing substantial insert, update, or delete operations on columns that are part of a FULLTEXT index in an InnoDB table. Set the configuration option innodb_optimize_fulltext_only=1 first. To keep the index maintenance period to a reasonable time, set the innodb_ft_num_word_optimize option to specify how many words to update in the search index, and run a sequence of OPTIMIZE TABLE statements until the search index is fully updated.

  • After deleting a large part of a MyISAM or ARCHIVE table, or making many changes to a MyISAM or ARCHIVE table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.

This statement requires SELECT and INSERT privileges for the table.

OPTIMIZE TABLE works for InnoDB, MyISAM, and ARCHIVE tables. OPTIMIZE TABLE is also supported for dynamic columns of in-memory NDB tables. It does not work for fixed-width columns of in-memory tables, nor does it work for Disk Data tables. The performance of OPTIMIZE on NDB Cluster tables can be tuned using --ndb_optimization_delay, which controls the length of time to wait between processing batches of rows by OPTIMIZE TABLE. For more information, see Previous NDB Cluster Issues Resolved in NDB Cluster 7.3.

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 22.3.4, “Maintenance of Partitions”.

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


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

TableThe table name
OpAlways optimize
Msg_typestatus, error, info, note, or warning
Msg_textAn informational message

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 .frm, .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                                                          |
| | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| | 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 and performed under the cover by ALTER TABLE ... FORCE is completed in place. An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation. 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) will. When considering whether or not to run optimize, consider the workload of transactions that your server will process:

MyISAM Details

For MyISAM tables, OPTIMIZE TABLE works as follows:

  1. If the table has deleted or split rows, repair the table.

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

  3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

Other Considerations

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)

User Comments
  Posted by Dathan Pattishall on May 25, 2004
myisamchk --quick --check-only-changed --sort-index --analyze

do a myisamchk on the table.
notice the deleted blocks in the right hand corner of the dialog. The stat still indicates a number > 0 for tables with deleted blocks.

myisamchk -r --sort-index --analyze *.MYI fixes that number. I'm inclined to believe the myisamchk *.MYI number since the table I'm "optimizing" does get a lot of deletes.

ALTER TABLE [tbl_name] TYPE=innodb
- will OPTIMIZE an INNODB table in the table space as well

  Posted by Mihail Manolov on October 29, 2004
Don't forget to FLUSH TABLES after execution of any of the following - REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on tables that are mapped into MERGE table.
  Posted by James Day on November 28, 2004
For InnoDB, if you have your tables in one tablespace, this will make a complete copy of the table within the tablespace, making the tablespace larger by the total table size less the free space you started with. It will not reduce the tablespace size. It can free fragmented space within a table to the tablespace, making that space available to other tables. If you're short of disk space and don't want to enlarge the tablespace you may be able to work around this by altering the table to MyISAM and then back to InnoDB.
  Posted by Jeff C on September 2, 2008
When using the InnoDB plugin with Barracuda+Compression, issue:

alter table your_table row_format=compressed; to rebuild the table.

  Posted by Ulrich Weiss on January 28, 2009
Also at MyISAM tables, the optimize needs a whole datafile of free hd space to free the not-used space in the file.
This means, that you need at least (database + data of biggest table) storage at the database directory (my case).

This may be very unfortunate, if you have (some, but) one very big table in your database, which needs almost all the storage... ;(

In this case, it is a very bad idea to optimize this table. Indeed, you can not optimize it, until the data of that big table is less then free space, which is very unlikely (This table must shrink to about eg. 1% its data).

Just tested: mysql will wait for space on the storage, if not enough is available for TABLE OPTIMIZE. Of course, this table will be locked all the time. I got out of this situation only by stopping the mysql server.

The only help I know, is to copy this table to another server with enough space, then optimized it there, and move this optimized table back (which must be done offline, because you have to remove the original first to get the space)
  Posted by Matthew Meyers on August 25, 2009
I wrote a quick dos script that seeks out fragmented tables and then runs optimize "in case anyone else can use it thought I would share". Used with MySQL 5.0.68 running on Server 2003.

:: Set Global Variables
set mysqlbin=D:\hyperic\mysql-5.0.68\bin
set mysqlhost=localhost
set mysqlport=3306
set mysqluser=*
set mysqlpw=*
set target_data_free=10

:: sql cmd to get a list of fragmented tables
set get_fragtables=%mysqlbin%\mysql -h%mysqlhost% -P%mysqlport% -u%mysqluser% -p%mysqlpw% --batch -Dinformation_schema --skip-column-names -e "SELECT table_schema, table_name FROM TABLES where table_schema not in ('information_schema','mysql') and data_free > %target_data_free%;"

:: loop all fragmented tables and optimize.
for /f "tokens=1,2* delims= " %%A in ('%get_fragtables%') do (
echo %mysqlbin%\mysql -h%mysqlhost% -P%mysqlport% -u%mysqluser% -p%mysqlpw% --batch -e "optimize table %%A.%%B;"

  Posted by Eelko de Vos on January 25, 2011
"Optimize table" locks the table, which made it impossible to use on the systems I worked with. We just couldn't wait for MySQL to clean up tens of tables which were huge (gigabytes) while the system was effectively down.

Thus I wrote my own "optimize table" perl-script: it builds the table from scratch while it still can be used by other scripts through a merge-table solution.

Of course there are a few cave-ats: see the explanation in the script. But for us this script turned out to be very very useful. We could clean tables while we could still use them as if nothing was happening.

Basically it sets up a new MyISAM merge-table and a copy of the old table, and then starts to fill a newly created clean table with all data from the old un-optimized table. At some point all data has been moved and the switch back is made: removal of the merge-table and renaming of the new and optimized table to the appropriate table-name.

Here's the link to the script:

  Posted by Rodolfo Campos on February 27, 2011
I've shared an script here for "automated" InnoDB tables optimization:

Hope you find it useful (it's in spanish).
  Posted by Neil Davis on February 10, 2016
Just an FYI.
Delete operations leave the table fragmented as we all know. After a delete operation, inserts may go into table out of order unless you OPTIMIZE [table] after the delete operation, before inserting. If you OPTIMIZE after a delete, and before inserting, the new rows will appear in the expected order after insertion.

I usually have a sort order field when the order is important, when I design a schema, but in an existing schema without a sort field, this can be useful to know if the sort order is important and you don't want the additional risk of modifying the schema.
Sign Up Login You must be logged in to post a comment.