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.
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.