The role of optimize table in MySQL

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.

1, first to see how many times the table index after the insert operation has been deleted

Mysql> SHOW INDEX from ' tbl_name ';
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+------- -+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+------- -+------+------------+---------+
| Tbl_name | 0 | PRIMARY | 1 | StepID | A | 1 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Flowid | 1 | Flowid | A | 1 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Wagercount | 1 | Wagercount | A | 1 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Wagerid_3 | 1 | Wagerid | A | 1 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Wagerid_3 | 2 | StepType | A | 1 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Wagerid_3 | 3 | Paramresult | A | 1 | 255 | NULL | | BTREE | |
| Tbl_name | 1 | steptype_2 | 1 | StepType | A | 1 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | steptype_2 | 2 | Paramresult | A | 1 | 255 | NULL | | BTREE | |
| Tbl_name | 1 | Wagerid_2 | 1 | Wagerid | A | 1 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Wagerid_2 | 2 | StepType | A | 1 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Wagerid_2 | 3 | Paramresult | A | 1 | 255 | NULL | | BTREE | |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+------- -+------+------------+---------+
Rows in Set (0.01 sec)

2. Optimization table

mysql> optimize table tbl_name;
+---------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| Test.tbl_name | Optimize | Status | OK |
+---------------+----------+----------+----------+
1 row in Set (40.60 sec)

3, then to see the effect of optimization

Mysql> SHOW INDEX from ' tbl_name ';
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+------- -+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+------- -+------+------------+---------+
| Tbl_name | 0 | PRIMARY | 1 | StepID | A | 172462 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Flowid | 1 | Flowid | A | 86231 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Wagercount | 1 | Wagercount | A | 4311 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Wagerid_3 | 1 | Wagerid | A | 86231 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Wagerid_3 | 2 | StepType | A | 172462 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Wagerid_3 | 3 | Paramresult | A | 172462 | 255 | NULL | | BTREE | |
| Tbl_name | 1 | steptype_2 | 1 | StepType | A | 9 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | steptype_2 | 2 | Paramresult | A | 86231 | 255 | NULL | | BTREE | |
| Tbl_name | 1 | Wagerid_2 | 1 | Wagerid | A | 86231 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Wagerid_2 | 2 | StepType | A | 172462 | NULL | NULL | | BTREE | |
| Tbl_name | 1 | Wagerid_2 | 3 | Paramresult | A | 172462 | 255 | NULL | | BTREE | |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+------- -+------+------------+---------+

Finally, take a look at the description of OPTIMIZE in the manual:

OPTIMIZE [LOCAL | No_write_to_binlog] TABLE tbl_name [, Tbl_name] ...

If you have deleted a large part of the table, or if you have made many changes to a table with variable-length rows (a table with a varchar, blob, or text column), you should use the
OPTIMIZE TABLE. The deleted records are kept in the linked list, and subsequent insert operations re-use the old record location. You can use optimize table to re-
Use unused space and defragment data files.

In most settings, you do not need to run optimize TABLE at all. Even if you have a large number of updates to variable-length lines, you do not need to run them frequently, once a week or once a month
, you can run only on a specific table.

OPTIMIZE table works only on MyISAM, BDB, and InnoDB tables.

Note that MySQL locks the table during the Optimize table run.

The role of optimize table in MySQL