Different cardinality in different indexes for the same column

[Sveta Smirnova]

Current algorithm in InnoDB uses random dives into pages when engine updates statistics of the table. Number of such pages can be defined by STATS_SAMPLE_PAGES option. However, if table has two or more indexes with same column, it may choose different pages for each of them, thus cardinality for the same column will vary which can lead to producing wrong plans by Optimizer.

Example:

create table t1(id int not null auto_increment primary key, f1 int, f2 int, f3 int, key(f1, f2), key(f1, f3)) engine = innodb stats_persistent=1 stats_auto_recalc=0 stats_sample_pages=1;

-- insert 8192 random rows into table t1

create table t2 like t1;
insert into t2 select * from t1 order by f1;
analyze table t2;
Table Op Msg_type Msg_text
test.t2 analyze status OK
select stat_value from mysql.innodb_index_stats where table_name='t2' and stat_description='f1';
stat_value
728
1391
show index from t2;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t2 0 PRIMARY 1 id A 6216 NULL NULL BTREE
t2 1 f1 1 f1 A 728 NULL NULL YES BTREE
t2 1 f1 2 f2 A 6216 NULL NULL YES BTREE
t2 1 f1_2 1 f1 A 1391 NULL NULL YES BTREE
t2 1 f1_2 2 f3 A 5668 NULL NULL YES BTREE
analyze table t2;
Table Op Msg_type Msg_text
test.t2 analyze status OK
select stat_value from mysql.innodb_index_stats where table_name='t2' and stat_description='f1';
stat_value
756
676
show index from t2;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t2 0 PRIMARY 1 id A 8764 NULL NULL BTREE
t2 1 f1 1 f1 A 756 NULL NULL YES BTREE
t2 1 f1 2 f2 A 6555 NULL NULL YES BTREE
t2 1 f1_2 1 f1 A 676 NULL NULL YES BTREE
t2 1 f1_2 2 f3 A 8764 NULL NULL YES BTREE
analyze table t2;
Table Op Msg_type Msg_text
test.t2 analyze status OK
select stat_value from mysql.innodb_index_stats where table_name='t2' and stat_description='f1';
stat_value
742
1339
show index from t2;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t2 0 PRIMARY 1 id A 8148 NULL NULL BTREE
t2 1 f1 1 f1 A 742 NULL NULL YES BTREE
t2 1 f1 2 f2 A 6555 NULL NULL YES BTREE
t2 1 f1_2 1 f1 A 1339 NULL NULL YES BTREE
t2 1 f1_2 2 f3 A 5668 NULL NULL YES BTREE
drop table t1, t2;

You see cardinality for index f1 is always different.

[Laurynas Biveinis (laurynas-biveinis)]

Superficially the initial key parts must be shared and not result in duplicate stat entries (with differing values), right? Since SHOW INDEX must return the index name, the same shared row should be returned several times?

How's the following case handled:

..., key(f2, f1), key(f3, f1), ...

That does not result in "f1" bogus individual cardinality stats computed, does it?

[Sveta Smirnova (svetasmirnova)]

Yes, this is correct. For f1 part of key(f2, f1), key(f3, f1) cardinality is same.

[Shahriyar Rzayev (rzayev-sehriyar)]

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