| Bug #95799 | index cardinality will not update after inserting many records | ||
|---|---|---|---|
| Submitted: | 14 Jun 2019 1:29 | Modified: | 14 Jun 2019 6:08 |
| Reporter: | tu ming | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 8.0.15 | OS: | CentOS |
| Assigned to: | CPU Architecture: | x86 | |
[14 Jun 2019 6:08]
MySQL Verification Team
Hello tu ming, Thank you for the report and test case. Imho this is not a bug, quoting from Dev's response in Bug#91038: The behavior seem to be correct, based on the default setting for information_schema_stats_expiry=86400 secs (= 1 day). This is introduced in 8.0 and not present in 5.7. The expectation stated in bug page can be observed with setting information_schema_stats_expiry=0. Please refer following page for more info, https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_in formation_schema_stats_expiry Test results that demonstrates the behavior: ```````````````````````````````````````````` Note that the INFORMATION_SCHEMA.TABLES.AUTO_INCREMENT value remains same until we set information_schema_stats_expiry=0. This will enable I_S to stop using the cached statistics value and always get latest statistics. - 8.0.16 - bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.16 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> set session information_schema_stats_expiry=0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'information_schema_stats_expiry'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | information_schema_stats_expiry | 0 | +---------------------------------+-------+ 1 row in set (0.00 sec) mysql> CREATE DATABASE bug; use bug; CREATE TABLE `t1` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `b` (`b`) ) ENGINE=InnoDB; delimiter ;; create procedure gen_data(start int, end int) begin declare i int; set i=start; while(i<=end)do insert into t1 values(i, i, i); Query OK, 1 row affected (0.00 sec) mysql> use bug; Database changed mysql> CREATE TABLE `t1` ( -> `id` int(11) NOT NULL, -> `a` int(11) DEFAULT NULL, -> `b` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `a` (`a`), -> KEY `b` (`b`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> delimiter ;; mysql> mysql> create procedure gen_data(start int, end int) -> begin -> declare i int; -> set i=start; -> while(i<=end)do -> insert into t1 values(i, i, i); -> set i=i+1; -> end while; -> end;; Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call gen_data(1,1000); Query OK, 1 row affected (0.46 sec) -- Waited for 1-4 seconds mysql> show index from t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t1 | 0 | PRIMARY | 1 | id | A | 1000 | NULL | NULL | | BTREE | | | YES | NULL | | t1 | 1 | a | 1 | a | A | 1000 | NULL | NULL | YES | BTREE | | | YES | NULL | | t1 | 1 | b | 1 | b | A | 1000 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 3 rows in set (0.00 sec) mysql> mysql> call gen_data(1001,2000); Query OK, 1 row affected (0.46 sec) -- Waited for 1-4 seconds mysql> show index from t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t1 | 0 | PRIMARY | 1 | id | A | 2000 | NULL | NULL | | BTREE | | | YES | NULL | | t1 | 1 | a | 1 | a | A | 2000 | NULL | NULL | YES | BTREE | | | YES | NULL | | t1 | 1 | b | 1 | b | A | 2000 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 3 rows in set (0.00 sec) regards, Umesh
Description: Index cardinality refers to the uniqueness of values stored in a specified column within an index. MySQL generates the index cardinality based on statistics stored as integers, therefore, the value may not be necessarily exact. The query optimizer uses the index cardinality to generate an optimal query plan for a given query. The cardinality should update once the records deleted or inserted beyond a threshold（default 10 percent). While I encountered a wrong behavior after inserting many records. The cardinality number is the same as before. It is ok on MySQL server 5.7.25. How to repeat: 1. Insert 1000 records, show index cardinality is 1000. 2. Insert more 1000 records, show index cardinality is still 1000. We expect this number bigger than 1000. CREATE DATABASE bug; use bug; CREATE TABLE `t1` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `b` (`b`) ) ENGINE=InnoDB; delimiter ;; create procedure gen_data(start int, end int) begin declare i int; set i=start; while(i<=end)do insert into t1 values(i, i, i); set i=i+1; end while; end;; delimiter ; call gen_data(1,1000); show index from t1; call gen_data(1001,2000); my history: mysql> call gen_data(1,1000); Query OK, 1 row affected (9.89 sec) mysql> show index from t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t1 | 0 | PRIMARY | 1 | id | A | 1000 | NULL | NULL | | BTREE | | | YES | NULL | | t1 | 1 | a | 1 | a | A | 1000 | NULL | NULL | YES | BTREE | | | YES | NULL | | t1 | 1 | b | 1 | b | A | 1000 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 3 rows in set (0.06 sec) mysql> call gen_data(1001,2000); Query OK, 1 row affected (8.40 sec) mysql> show index from t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t1 | 0 | PRIMARY | 1 | id | A | 1000 | NULL | NULL | | BTREE | | | YES | NULL | | t1 | 1 | a | 1 | a | A | 1000 | NULL | NULL | YES | BTREE | | | YES | NULL | | t1 | 1 | b | 1 | b | A | 1000 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 3 rows in set (0.00 sec) mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 2000 | +----------+ 1 row in set (0.02 sec)