Bug #108110 optimize innodb table does not update statistics tables

• Submitted: 10 Aug 2022 12:54
• Modified: 10 Aug 2022 13:42
• Reporter: Cheng Zhou
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[10 Aug 2022 12:54] Cheng Zhou

Description:
After we do 'OPTIMIZE TABLE ...' for an innodb table, the statistics of this table is not updated.  While the Msg_test says, "Table does not support optimize, doing recreate + analyze instead". "ANALYZE TABLE ..." statement can update statistics tables immediately. 

How to repeat:
mysql> select data_free from information_schema.tables where table_schema='db1' and table_name ='t1';
+-----------+
| DATA_FREE |
+-----------+
| 477102080 |
+-----------+
1 row in set (0.00 sec)

mysql> optimize table t1;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| db1.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| db1.t1 | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.03 sec)

mysql> select data_free from information_schema.tables where table_schema='db1' and table_name ='t1';
+-----------+
| DATA_FREE |
+-----------+
| 477102080 |
+-----------+
1 row in set (0.01 sec)

mysql> analyze table t1;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| db1.t1 | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> select data_free from information_schema.tables where table_schema='db1' and table_name ='t1';
+-----------+
| DATA_FREE |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

[10 Aug 2022 13:42] MySQL Verification Team

Hi Mr. Zhou,

Thank you for your bug report.

However, it is not a bug.

As the message says, that command is only performing ANALYZE on the table. That means that only index statistics are updated, for the major nodes. That does not and never will impact the free space in the tablespace. Free space is calculated on the number of pages that do not contain data. Hence, those two are totally independent. This is a full explanation.

Not a bug.