enterprise-support team mailing list archive
-
enterprise-support team
-
Mailing list archive
-
Message #04497
[Bug 1484311] Re: Wrong cardinality for InnoDB table
Confirmed with PS 5.6
nilnandan@desktop:~/Downloads$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f
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> 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>
mysql> create table t1(f1 int, f2 int, key(f1), key f1_2 (f1, f2)) engine=innodb;
ERROR 1050 (42S01): Table 't1' already exists
mysql>
mysql> drop table t1
->
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> create table t1(f1 int, f2 int, key(f1), key f1_2 (f1, f2)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1(f1) values (1),(2),(3),(4),(5),(6);
Query OK, 6 rows affected (0.01 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.01 sec)
Records: 24 Duplicates: 0 Warnings: 0
mysql> insert into t1 (f1) select f1 from t1;
Query OK, 48 rows affected (0.00 sec)
Records: 48 Duplicates: 0 Warnings: 0
mysql> insert into t1 (f1) select f1 from t1;
Query OK, 96 rows affected (0.00 sec)
Records: 96 Duplicates: 0 Warnings: 0
mysql> insert into t1 (f1) select f1 from t1;
Query OK, 192 rows affected (0.00 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, 1 warning (0.01 sec)
Rows matched: 768 Changed: 768 Warnings: 1
mysql>
mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
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.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 |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_diff_pfx01 | 3 | 2 | db |
| percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_diff_pfx02 | 29 | 2 | db,tbl |
| percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_diff_pfx03 | 109 | 2 | db,tbl,chunk |
| percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_leaf_pages | 2 | NULL | Number of leaf pages in the index |
| percona | checksums | PRIMARY | 2015-08-12 13:07:19 | size | 3 | NULL | Number of pages in the index |
| percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx01 | 49 | 1 | ts |
| percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx02 | 50 | 1 | ts,db |
| percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx03 | 76 | 1 | ts,db,tbl |
| percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx04 | 109 | 1 | ts,db,tbl,chunk |
| percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | size | 1 | NULL | Number of pages in the index |
| source_db | altertest | PRIMARY | 2015-07-20 12:44:07 | n_diff_pfx01 | 0 | 1 | id |
| source_db | altertest | PRIMARY | 2015-07-20 12:44:07 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| source_db | altertest | PRIMARY | 2015-07-20 12:44:07 | size | 1 | NULL | Number of pages in the index |
| test | nil | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | n_diff_pfx01 | 10 | 1 | DB_ROW_ID |
| test | nil | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | nil | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | size | 1 | NULL | Number of pages in the index |
| test | t1 | GEN_CLUST_INDEX | 2015-08-14 13:29:37 | n_diff_pfx01 | 768 | 3 | DB_ROW_ID |
| test | t1 | GEN_CLUST_INDEX | 2015-08-14 13:29:37 | n_leaf_pages | 3 | NULL | Number of leaf pages in the index |
| test | t1 | GEN_CLUST_INDEX | 2015-08-14 13:29:37 | size | 4 | NULL | Number of pages in the index |
| test | t1 | f1 | 2015-08-14 13:29:37 | n_diff_pfx01 | 6 | 1 | f1 |
| test | t1 | f1 | 2015-08-14 13:29:37 | n_diff_pfx02 | 768 | 1 | f1,DB_ROW_ID |
| test | t1 | f1 | 2015-08-14 13:29:37 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | t1 | f1 | 2015-08-14 13:29:37 | size | 1 | NULL | Number of pages in the index |
| test | t1 | f1_2 | 2015-08-14 13:29:37 | n_diff_pfx01 | 6 | 1 | f1 |
| test | t1 | f1_2 | 2015-08-14 13:29:37 | n_diff_pfx02 | 12 | 1 | f1,f2 |
| test | t1 | f1_2 | 2015-08-14 13:29:37 | n_diff_pfx03 | 768 | 1 | f1,f2,DB_ROW_ID |
| test | t1 | f1_2 | 2015-08-14 13:29:37 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | t1 | f1_2 | 2015-08-14 13:29:37 | size | 1 | NULL | Number of pages in the index |
| test | t2 | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| test | t2 | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | t2 | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | size | 1 | NULL | Number of pages in the index |
| test | t3 | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| test | t3 | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | t3 | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | size | 1 | NULL | Number of pages in the index |
| test | testb | PRIMARY | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | a |
| test | testb | PRIMARY | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | testb | PRIMARY | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index |
| test | testb | fk2 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | e |
| test | testb | fk2 | 2015-07-24 09:45:39 | n_diff_pfx02 | 0 | 1 | e,a |
| test | testb | fk2 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | testb | fk2 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index |
| test | testb | uk1 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | b |
| test | testb | uk1 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | testb | uk1 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index |
| test | testb | uk2 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | c |
| test | testb | uk2 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | testb | uk2 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index |
| test | testb | uk3 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | d |
| test | testb | uk3 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | testb | uk3 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
51 rows in set (0.00 sec)
mysql>
mysql> alter table t1 engine=myisam;
Query OK, 768 rows affected (0.01 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.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 | 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 |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_diff_pfx01 | 3 | 2 | db |
| percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_diff_pfx02 | 29 | 2 | db,tbl |
| percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_diff_pfx03 | 109 | 2 | db,tbl,chunk |
| percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_leaf_pages | 2 | NULL | Number of leaf pages in the index |
| percona | checksums | PRIMARY | 2015-08-12 13:07:19 | size | 3 | NULL | Number of pages in the index |
| percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx01 | 49 | 1 | ts |
| percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx02 | 50 | 1 | ts,db |
| percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx03 | 76 | 1 | ts,db,tbl |
| percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx04 | 109 | 1 | ts,db,tbl,chunk |
| percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | size | 1 | NULL | Number of pages in the index |
| source_db | altertest | PRIMARY | 2015-07-20 12:44:07 | n_diff_pfx01 | 0 | 1 | id |
| source_db | altertest | PRIMARY | 2015-07-20 12:44:07 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| source_db | altertest | PRIMARY | 2015-07-20 12:44:07 | size | 1 | NULL | Number of pages in the index |
| test | nil | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | n_diff_pfx01 | 10 | 1 | DB_ROW_ID |
| test | nil | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | nil | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | size | 1 | NULL | Number of pages in the index |
| test | t2 | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| test | t2 | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | t2 | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | size | 1 | NULL | Number of pages in the index |
| test | t3 | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| test | t3 | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | t3 | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | size | 1 | NULL | Number of pages in the index |
| test | testb | PRIMARY | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | a |
| test | testb | PRIMARY | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | testb | PRIMARY | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index |
| test | testb | fk2 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | e |
| test | testb | fk2 | 2015-07-24 09:45:39 | n_diff_pfx02 | 0 | 1 | e,a |
| test | testb | fk2 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | testb | fk2 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index |
| test | testb | uk1 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | b |
| test | testb | uk1 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | testb | uk1 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index |
| test | testb | uk2 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | c |
| test | testb | uk2 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | testb | uk2 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index |
| test | testb | uk3 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | d |
| test | testb | uk3 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | testb | uk3 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
39 rows in set (0.00 sec)
mysql>
mysql>
** Changed in: percona-server/5.6
Status: New => Confirmed
** Bug watch added: MySQL Bug System #78066
http://bugs.mysql.com/bug.php?id=78066
** Also affects: mysql-server via
http://bugs.mysql.com/bug.php?id=78066
Importance: Unknown
Status: Unknown
** Tags added: upstream
--
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1484311
Title:
Wrong cardinality for InnoDB table
To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1484311/+subscriptions
