Function of optimize table in MySQL

Source: Internet
Author: User

After a large amount of data is deleted from your database, you may find that the size of the data file is not reduced. This is because fragments are left in the data file after the delete operation. Discuz! The data table optimization function is provided on the system number settings page to remove the data file fragments left after the delete operation, reduce the file size, and accelerate future read/write operations. You only need to delete data tables in batches or optimize data tables on a regular basis (such as every one or two months.

Optimize table works by making a temporary copy of the original table

Optimize table syntax

Optimize [local | no_write_to_binlog] Table tbl_name [, tbl_name]...

If you have deleted a majority of the tables, or if you have made many changes to the tables that contain variable-length rows (tables that contain varchar, blob, or text columns, optimize table should be used. The deleted records are kept in the Link List. Subsequent insert operations will reuse the old record location. You can use optimize table to reuse unused space and organize data file fragments.

In most settings, you do not need to run optimize table. Even if you have made a large number of updates to a variable-length row, you do not need to run it frequently, once a week or once a month, and only run on a specific table.

Optimize table only applies to MyISAM, bdb, and InnoDB tables.

For MyISAM tables, optimize tables are operated as follows:

1. If the table has been deleted or broken down, the table will be repaired.

2. If the index page is not classified, the page is classified.

3. If the statistical data of the table is not updated (and cannot be repaired by classifying the index), update the data.

For bdb tables, optimize table is currently mapped to analyze table. For InnoDB tables, optimize table is mapped to alter table, which will recreate the table. The rebuilding operation can update index statistics and release unused space in the Cluster Index. See section "analyze table Syntax ".

Use the-Skip-new or-safe-mode option to start mysqld. By starting mysqld, you can enable optimize table to work for other table types.

Note: During the optimize table operation, MySQL locks the table.

The optimize TABLE statement is written to the binary log unless the optional no_write_to_binlog keyword (or its alias is local) is used ). Therefore, the optimize table command for MySQL Server serves as a replication master server. By default, these commands will be copied to the replication slave server.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.