Description:
Hi MySQL Team!
When you add or remove an index, for some reason, rows statistics available in sys schema are reset for a table.
How to repeat:
mysql > select @@version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment |
+-----------+------------------------------+
| 8.0.34 | MySQL Community Server - GPL |
+-----------+------------------------------+
1 row in set (0.00 sec)
mysql > create table t1 (id int, a int);
Query OK, 0 rows affected (0.02 sec)
mysql > insert into t1 values (1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql > delete from t1 limit 1;
Query OK, 1 row affected (0.01 sec)
mysql > select table_schema,table_name,rows_fetched,rows_inserted,rows_updated,rows_deleted,io_write_requests from sys.schema_table_statistics where table_schema='db1';
+--------------+------------+--------------+---------------+--------------+--------------+-------------------+
| table_schema | table_name | rows_fetched | rows_inserted | rows_updated | rows_deleted | io_write_requests |
+--------------+------------+--------------+---------------+--------------+--------------+-------------------+
| db1 | t1 | 1 | 3 | 0 | 1 | 10 |
+--------------+------------+--------------+---------------+--------------+--------------+-------------------+
1 row in set (0.01 sec)
mysql > alter table t1 add key(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > select table_schema,table_name,rows_fetched,rows_inserted,rows_updated,rows_deleted,io_write_requests from sys.schema_table_statistics where table_schema='db1';
+--------------+------------+--------------+---------------+--------------+--------------+-------------------+
| table_schema | table_name | rows_fetched | rows_inserted | rows_updated | rows_deleted | io_write_requests |
+--------------+------------+--------------+---------------+--------------+--------------+-------------------+
| db1 | t1 | 0 | 0 | 0 | 0 | 17 |
+--------------+------------+--------------+---------------+--------------+--------------+-------------------+
1 row in set (0.00 sec)
Suggested fix:
Preserve row stats during schema changes.
