Description:
Under certain circumstances SHOW INDEXES from InnoDB tables shows wrong cardinality while mysql.innodb_index_stats contains correct information.
Originally reported here: https://bugs.launchpad.net/percona-server/+bug/1484311
How to repeat:
nilnandan.joshi@bm-support01:~/sandboxes$ mysql -uroot -p --socket=/tmp/mysql_sandbox15626.sock
Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.26 MySQL Community Server (GPL)
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, 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>
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table t1(f1 int, f2 int, key(f1), key f1_2 (f1, f2)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1(f1) values (1),(2),(3),(4),(5),(6);
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into t1 (f1) select f1 from t1;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into t1 (f1) select f1 from t1;
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> insert into t1 (f1) select f1 from t1;
Query OK, 24 rows affected (0.00 sec)
Records: 24 Duplicates: 0 Warnings: 0
mysql> insert into t1 (f1) select f1 from t1;
Query OK, 48 rows affected (0.01 sec)
Records: 48 Duplicates: 0 Warnings: 0
mysql> insert into t1 (f1) select f1 from t1;
Query OK, 96 rows affected (0.01 sec)
Records: 96 Duplicates: 0 Warnings: 0
mysql> insert into t1 (f1) select f1 from t1;
Query OK, 192 rows affected (0.01 sec)
Records: 192 Duplicates: 0 Warnings: 0
mysql> insert into t1 (f1) select f1 from t1;
Query OK, 384 rows affected (0.01 sec)
Records: 384 Duplicates: 0 Warnings: 0
mysql> update t1 set f2=rand();
Query OK, 768 rows affected (0.08 sec)
Rows matched: 768 Changed: 768 Warnings: 0
mysql>
mysql> analyze table t1;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 1 | f1 | 1 | f1 | A | 12 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | f1_2 | 1 | f1 | A | 12 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | f1_2 | 2 | f2 | A | 24 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)
mysql> select count(distinct f1), count(distinct f2), count(distinct f1, f2), count(*) from t1;
+--------------------+--------------------+------------------------+----------+
| count(distinct f1) | count(distinct f2) | count(distinct f1, f2) | count(*) |
+--------------------+--------------------+------------------------+----------+
| 6 | 2 | 12 | 768 |
+--------------------+--------------------+------------------------+----------+
1 row in set (0.02 sec)
mysql> select * from mysql.innodb_index_stats;
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test | sbtest1 | PRIMARY | 2015-08-12 08:36:15 | n_diff_pfx01 | 295992 | 20 | id |
| test | sbtest1 | PRIMARY | 2015-08-12 08:36:15 | n_leaf_pages | 4111 | NULL | Number of leaf pages in the index |
| test | sbtest1 | PRIMARY | 2015-08-12 08:36:15 | size | 4133 | NULL | Number of pages in the index |
| test | sbtest1 | k_1 | 2015-08-12 08:36:15 | n_diff_pfx01 | 54341 | 20 | k |
| test | sbtest1 | k_1 | 2015-08-12 08:36:15 | n_diff_pfx02 | 307824 | 20 | k,id |
| test | sbtest1 | k_1 | 2015-08-12 08:36:15 | n_leaf_pages | 363 | NULL | Number of leaf pages in the index |
| test | sbtest1 | k_1 | 2015-08-12 08:36:15 | size | 417 | NULL | Number of pages in the index |
| test | sbtest2 | PRIMARY | 2015-08-12 08:37:11 | n_diff_pfx01 | 295992 | 20 | id |
| test | sbtest2 | PRIMARY | 2015-08-12 08:37:11 | n_leaf_pages | 4111 | NULL | Number of leaf pages in the index |
| test | sbtest2 | PRIMARY | 2015-08-12 08:37:11 | size | 4133 | NULL | Number of pages in the index |
| test | sbtest2 | k_1 | 2015-08-12 08:37:11 | n_diff_pfx01 | 49603 | 20 | k |
| test | sbtest2 | k_1 | 2015-08-12 08:37:11 | n_diff_pfx02 | 298640 | 20 | k,id |
| test | sbtest2 | k_1 | 2015-08-12 08:37:11 | n_leaf_pages | 363 | NULL | Number of leaf pages in the index |
| test | sbtest2 | k_1 | 2015-08-12 08:37:11 | size | 417 | NULL | Number of pages in the index |
| test | t1 | GEN_CLUST_INDEX | 2015-08-14 04:05:37 | n_diff_pfx01 | 768 | 3 | DB_ROW_ID |
| test | t1 | GEN_CLUST_INDEX | 2015-08-14 04:05:37 | n_leaf_pages | 3 | NULL | Number of leaf pages in the index |
| test | t1 | GEN_CLUST_INDEX | 2015-08-14 04:05:37 | size | 4 | NULL | Number of pages in the index |
| test | t1 | f1 | 2015-08-14 04:05:37 | n_diff_pfx01 | 6 | 1 | f1 |
| test | t1 | f1 | 2015-08-14 04:05:37 | n_diff_pfx02 | 768 | 1 | f1,DB_ROW_ID |
| test | t1 | f1 | 2015-08-14 04:05:37 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | t1 | f1 | 2015-08-14 04:05:37 | size | 1 | NULL | Number of pages in the index |
| test | t1 | f1_2 | 2015-08-14 04:05:37 | n_diff_pfx01 | 6 | 1 | f1 |
| test | t1 | f1_2 | 2015-08-14 04:05:37 | n_diff_pfx02 | 12 | 1 | f1,f2 |
| test | t1 | f1_2 | 2015-08-14 04:05:37 | n_diff_pfx03 | 768 | 1 | f1,f2,DB_ROW_ID |
| test | t1 | f1_2 | 2015-08-14 04:05:37 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | t1 | f1_2 | 2015-08-14 04:05:37 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
26 rows in set (0.01 sec)
mysql>
mysql> alter table t1 engine=myisam;
Query OK, 768 rows affected (0.05 sec)
Records: 768 Duplicates: 0 Warnings: 0
mysql>
mysql> analyze table t1;
+---------+---------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+-----------------------------+
| test.t1 | analyze | status | Table is already up to date |
+---------+---------+----------+-----------------------------+
1 row in set (0.01 sec)
mysql> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 1 | f1 | 1 | f1 | A | 6 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | f1_2 | 1 | f1 | A | 6 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | f1_2 | 2 | f2 | A | 12 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> select count(distinct f1), count(distinct f2), count(distinct f1, f2), count(*) from t1;
+--------------------+--------------------+------------------------+----------+
| count(distinct f1) | count(distinct f2) | count(distinct f1, f2) | count(*) |
+--------------------+--------------------+------------------------+----------+
| 6 | 2 | 12 | 768 |
+--------------------+--------------------+------------------------+----------+
1 row in set (0.00 sec)
mysql> select * from mysql.innodb_index_stats;
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test | sbtest1 | PRIMARY | 2015-08-12 08:36:15 | n_diff_pfx01 | 295992 | 20 | id |
| test | sbtest1 | PRIMARY | 2015-08-12 08:36:15 | n_leaf_pages | 4111 | NULL | Number of leaf pages in the index |
| test | sbtest1 | PRIMARY | 2015-08-12 08:36:15 | size | 4133 | NULL | Number of pages in the index |
| test | sbtest1 | k_1 | 2015-08-12 08:36:15 | n_diff_pfx01 | 54341 | 20 | k |
| test | sbtest1 | k_1 | 2015-08-12 08:36:15 | n_diff_pfx02 | 307824 | 20 | k,id |
| test | sbtest1 | k_1 | 2015-08-12 08:36:15 | n_leaf_pages | 363 | NULL | Number of leaf pages in the index |
| test | sbtest1 | k_1 | 2015-08-12 08:36:15 | size | 417 | NULL | Number of pages in the index |
| test | sbtest2 | PRIMARY | 2015-08-12 08:37:11 | n_diff_pfx01 | 295992 | 20 | id |
| test | sbtest2 | PRIMARY | 2015-08-12 08:37:11 | n_leaf_pages | 4111 | NULL | Number of leaf pages in the index |
| test | sbtest2 | PRIMARY | 2015-08-12 08:37:11 | size | 4133 | NULL | Number of pages in the index |
| test | sbtest2 | k_1 | 2015-08-12 08:37:11 | n_diff_pfx01 | 49603 | 20 | k |
| test | sbtest2 | k_1 | 2015-08-12 08:37:11 | n_diff_pfx02 | 298640 | 20 | k,id |
| test | sbtest2 | k_1 | 2015-08-12 08:37:11 | n_leaf_pages | 363 | NULL | Number of leaf pages in the index |
| test | sbtest2 | k_1 | 2015-08-12 08:37:11 | size | 417 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
14 rows in set (0.01 sec)
mysql>
mysql>
