Bug #78066 Wrong cardinality for InnoDB table in MySQL
Submitted: 14 Aug 2015 8:11 Modified: 14 Aug 2015 8:54
Reporter: Nilnandan Joshi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6.26, 5.6.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: cardinality, innodb

[14 Aug 2015 8:11] Nilnandan Joshi
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>
[14 Aug 2015 8:54] Umesh Shastry
Hello Joshi,

Thank you for the report.
Observed this with 5.6.26/5.6.27 builds(5.7.8 doesn't seems to be affected).

Thanks,
Umesh
[8 Mar 2017 7:51] Przemyslaw Malkowski
5.6.25 is still affected.

5.7.17 shows correct statistics in both show indexes and innodb_index_stats.
[8 Mar 2017 7:53] Przemyslaw Malkowski
Sorry, it was meant to be 5.6.35