MySQL's optimize table command

Source: Internet
Author: User

Sometimes, when using MySQL, you may find that even though a table deletes a lot of data, the data and index files of this table are surprisingly small. This is because when MySQL deletes data (especially text and blobs), it leaves a lot of data holes that occupy the space of the original data, so the size of the file does not change. These voids may be re-used when inserting data later on, and of course it may be there. This void not only adds additional storage costs, but also reduces the efficiency of the table's scanning because of fragmentation of data.

MySQL provides a way to solve this problem: the Optimize table table_name command. The command will defragment the table to remove the holes. View before and after effects can use the Show Table Status command, such as show table status from [database] LIKE ' [table_name] '; Returns the data_free in the result is the storage space occupied by the hole. When you finish executing the Optimize table command, the value of the field will be 0.

A table of type InnoDB cannot be used with the Optimize table command. Forced use will return the following results:

However, using ALTER TABLE table_name ENGINE=INNODB can also achieve the same effect of releasing voids. This is because when the data engine is converted (even if there is no real conversion), MySQL reads the data from the table and then writes it back, and in this process, the void is naturally lost.

Optimze table is very fast in the case of a table with a small amount of data, but it is tragic to have a table with a large number of data optimze, because the execution time will be long and the table will be locked. At this time, consideration should be given to the use of some operational measures to avoid the impact of existing network services.


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: info-contact@alibabacloud.com 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.