MySQL optimize table command Test example

Today, after the bulk deletion of the Myiam datasheet, found that there is no recycling, after checking the data, found to be through the optimize table to reclaim space
Test the following to establish a data table:

The code is as follows	Copy Code
CREATE TABLE ' Ttext ' ( ' id ' int (one) DEFAULT NULL, ' Context ' text ) Engine=myisam DEFAULT charset=latin1 INSERT into Ttext values (1, ' tiger1′), (2, ' tiger2′), (3, ' tiger3′);

The INSERT INTO Ttext SELECT * Ttext is then repeated, so that the data file is 240MB, and it is possible to confirm that the data with ID 1 occupies 1/3 of the space, and then you can delete the delete from Ttext where id=1;
According to the normal idea, after such an operation, the space should have recycled, but the fact is not.
OPTIMIZE TABLE should be used if to have deleted a large part A
Table or if you are have made many changes to a table with Variable-length
Rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns).
can use OPTIMIZE TABLE to
Reclaim the unused spaces and to defragment the data file.
Through the data can be seen through the Optimize table Ttext to reclaim the corresponding space.
After continuing to look at the official manual, it was found that the operation was very much related to the engine used by the table.
The InnoDB table must be a row that uses a separate tablespace, followed by the mysql5.1.27 start optimize the table is also supported for partitioned tables. You can optimize the partition table.
for the MyISAM engine, using optimize table also has the following features:
If the table has deleted or split rows, repair the table. [Repair Table]
If The index pages are not sorted, sort them. [Index not sorted, sort]
If The table ' s statistics are not up to date (and the repair could not is accomplished by sorting the index), update them . [Update it if the table's statistics are not up to date]