Wrong cardinality for InnoDB table

Bug #1484311 reported by Sveta Smirnova on 2015-08-13
16
This bug affects 2 people
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.6
Triaged
Medium
Unassigned
5.7
Invalid
Undecided
Unassigned

Bug Description

Under certain circumstances SHOW INDEXES from InnoDB tables shows wrong cardinality while mysql.innodb_index_stats contains correct information.

Test case:

--source include/have_innodb.inc

create table t1(f1 int, f2 int, key(f1), key f1_2 (f1, f2)) engine=innodb;
insert into t1(f1) values (1),(2),(3),(4),(5),(6);
insert into t1 (f1) select f1 from t1;
insert into t1 (f1) select f1 from t1;
insert into t1 (f1) select f1 from t1;
insert into t1 (f1) select f1 from t1;
insert into t1 (f1) select f1 from t1;
insert into t1 (f1) select f1 from t1;
insert into t1 (f1) select f1 from t1;

update t1 set f2=rand();

analyze table t1;
show indexes from t1;
select count(distinct f1), count(distinct f2), count(distinct f1, f2), count(*) from t1;
select * from mysql.innodb_index_stats;

alter table t1 engine=myisam;

analyze table t1;
show indexes from t1;
select count(distinct f1), count(distinct f2), count(distinct f1, f2), count(*) from t1;
select * from mysql.innodb_index_stats;

Bug does not exist in MyISAM and version 5.7

Upstream?

Download full text (21.8 KiB)

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. |...

tags: added: upstream

Reported upstream.

Przemek (pmalkowski) on 2017-03-07
tags: added: i167478
tags: added: i193089
Yura Sorokin (yura-sorokin) wrote :

Another observation:
If the table is created with 'STATS_PERSISTENT=0' option.
The results of 'SHOW INDEXES FROM t1' are even stranger

***************************************************************************************************
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 10 NULL NULL YES BTREE
t1 1 f1_2 2 f2 A 35 NULL NULL YES BTREE
***************************************************************************************************

To sum up
MyISAM / expected : ( 6, 6, 12)
InnoDB STATS_PERSISTENT=1 : (12, 12, 24)
InnoDB STATS_PERSISTENT=0 : (12, 10, 35)

Yura Sorokin (yura-sorokin) wrote :

Here is a simplified test case (1 field, 1 index and 1 INSERT SELECT)

******************************************************
--source include/have_innodb.inc

CREATE TABLE t1(f1 INT, key(f1)) STATS_PERSISTENT=1 ENGINE=InnoDB;
INSERT INTO t1(f1) VALUES (1), (2), (3), (4), (5), (6);

ANALYZE TABLE t1;
SELECT COUNT(DISTINCT f1), COUNT(*) FROM t1;
SELECT INDEX_SCHEMA, INDEX_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 't1';

INSERT INTO t1(f1) SELECT f1 FROM t1;

ANALYZE TABLE t1;
SELECT COUNT(DISTINCT f1), COUNT(*) FROM t1;
SELECT INDEX_SCHEMA, INDEX_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 't1';

DROP TABLE t1;
******************************************************

After the 'INSERT INTO t1(f1) SELECT f1 FROM t1' statement index cardinality changes from expected 6 to 12.

Yura Sorokin (yura-sorokin) wrote :

It looks like in InnoDB 5.6 this behavior is by design. Here is an excerpt from 'ha_innobase::info_low()' ('ha_innodb.cc')

********************************************
rec_per_key = innodb_rec_per_key(
  index, j, stats.records);

/* Since MySQL seems to favor table scans
too much over index searches, we pretend
index selectivity is 2 times better than
our estimate: */

rec_per_key = rec_per_key / 2;

if (rec_per_key == 0) {
 rec_per_key = 1;
}
********************************************

Yura Sorokin (yura-sorokin) wrote :

In 5.7 this fragment is partially reworked, so this is why we get expected results there.

Sveta Smirnova (svetasmirnova) wrote :

Bug #1733520 was marked as duplicate of this one.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1642

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.