Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
11.5.2, 10.6.21
-
None
Description
Executing a RENAME TABLE command in MariaDB does not trigger recalculation or updating of table statistics. This causes the renamed table to retain outdated or manually set incorrect statistics, leading to incorrect query optimizer decisions and potential performance degradation.
Doc: https://mariadb.com/kb/en/rename-table/
For InnoDB tables, it also triggers a reload of InnoDB statistics.
Doc: https://mariadb.com/kb/en/innodb-persistent-statistics/
.The ANALYZE TABLE statement can be used to reload InnoDB statistics. RENAME TABLE has the same effect, triggering a reload of the statistics
Demo:
MariaDB [test]> CREATE TABLE test.products (
|
-> product_id INT PRIMARY KEY,
|
-> sku VARCHAR(50), |
-> price DECIMAL(10,2), |
-> category_id INT,
|
-> supplier_id INT,
|
-> stock_quantity INT
|
-> );
|
Query OK, 0 rows affected (0.002 sec) |
|
|
MariaDB [test]>
|
MariaDB [test]> INSERT INTO test.products (product_id, sku, price, category_id, supplier_id, stock_quantity)
|
-> SELECT seq, CONCAT('SKU', seq), ROUND(RAND()*100,2), seq % 5, seq % 3, FLOOR(RAND()*50) |
-> FROM seq_1_to_1000000; -- Replace with appropriate sequence generator if necessary |
Query OK, 1000000 rows affected (3.705 sec) |
Records: 1000000 Duplicates: 0 Warnings: 0 |
|
|
MariaDB [test]>
|
MariaDB [test]> select * from mysql.innodb_index_stats where table_name='products'; |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
|
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
|
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
|
| test | products | PRIMARY | 2025-04-04 09:09:43 | n_diff_pfx01 | 11 | 1 | product_id | |
| test | products | PRIMARY | 2025-04-04 09:09:43 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | |
| test | products | PRIMARY | 2025-04-04 09:09:43 | size | 1 | NULL | Number of pages in the index | |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
|
3 rows in set (0.001 sec) |
|
|
MariaDB [test]> UPDATE mysql.innodb_index_stats SET stat_value=2 WHERE table_name='products'; |
Query OK, 3 rows affected (0.001 sec) |
Rows matched: 3 Changed: 3 Warnings: 0 |
|
|
MariaDB [test]> FLUSH TABLES;
|
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> SHOW INDEXES FROM test.products;
|
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
|
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
| products | 0 | PRIMARY | 1 | product_id | A | 4 | NULL | NULL | | BTREE | | | NO | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
1 row in set (0.001 sec) |
|
|
MariaDB [test]> select * from mysql.innodb_index_stats where table_name='products'; |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
|
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
|
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
|
| test | products | PRIMARY | 2025-04-04 09:12:24 | n_diff_pfx01 | 2 | 20 | product_id | |
| test | products | PRIMARY | 2025-04-04 09:12:24 | n_leaf_pages | 2 | NULL | Number of leaf pages in the index | |
| test | products | PRIMARY | 2025-04-04 09:12:24 | size | 2 | NULL | Number of pages in the index | |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
|
3 rows in set (0.001 sec) |
|
|
MariaDB [test]> RENAME TABLE test.products TO test.items;
|
Query OK, 0 rows affected (0.005 sec) |
|
|
MariaDB [test]> SHOW INDEXES FROM test.items;
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
| items | 0 | PRIMARY | 1 | product_id | A | 4 | NULL | NULL | | BTREE | | | NO | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
1 row in set (0.001 sec) |
|
|
MariaDB [test]> ANALYZE TABLE test.items;
|
+------------+---------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+------------+---------+----------+----------+
|
| test.items | analyze | status | OK |
|
+------------+---------+----------+----------+
|
1 row in set (0.002 sec) |
|
|
MariaDB [test]> SHOW INDEXES FROM test.items;
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
| items | 0 | PRIMARY | 1 | product_id | A | 996789 | NULL | NULL | | BTREE | | | NO | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
1 row in set (0.000 sec) |