MySQL Bugs: #117426: optimize table does not update table and index stats

Bug #117426	optimize table does not update table and index stats
Submitted:	10 Feb 12:10	Modified:	11 Feb 6:44
Reporter:	Huaxiong Song (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	8.0.41	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
The documentation and code describe that for some engines, such as innodb, opimize = recreate + analyze.
As we all know, analyze will update some information of tables and indexes, but optimize table will not update them immediately, at least from the query results, they are not updated.
To describe this problem from another angle, optimize table is usually regarded as a universal solution for reclaiming table space, but if we query data_free in information_schema.tables immediately after optimize table, we will find that it has not changed.

How to repeat:
Run the test case, we can get the above result.

--echo #
--echo # 1) Prepare
--echo #
CREATE DATABASE my_test;
USE my_test;

# table status of t1 and t2 will be significantly different
CREATE TABLE t1 (id INT);

INSERT INTO t1 VALUES (1);
DELIMITER $$;

CREATE PROCEDURE insert_n_times(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= n DO
        INSERT INTO t1 SELECT * FROM t1;
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;$$

CALL insert_n_times(16);

--echo #
--echo # 2) SHOW TABLE STATUS
--echo #
SELECT DATA_FREE FROM information_schema.tables WHERE table_name = 't1';

--echo #
--echo # 3) Optimize and SHOW TABLE STATUS
--echo #
OPTIMIZE TABLE t1;
SELECT DATA_FREE FROM information_schema.tables WHERE table_name = 't1';

--echo #
--echo # 4) ANALYZE TABLE and SHOW TABLE STATUS
--echo #
ANALYZE TABLE t1, t1_tmp;
SELECT DATA_FREE FROM information_schema.tables WHERE table_name = 't1';

--echo #
--echo # 5) Cleanup
--echo #
DROP DATABASE my_test;

Suggested fix:
When call optimize table, ha_analyze is called directly, but the part about updating the statistics of table and index is missing. I think it should be supplemented here.

Hello Huaxiong Song,

Thank you for the report and test case.

regards,
Umesh

fix of Bug#117426 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bugfix_117426.patch (application/octet-stream, text), 5.99 KiB.

Thank you very much for your patch contribution, we appreciate it!

regards,
Umesh