Description:
Originally reported at https://bugs.launchpad.net/percona-server/+bug/1715744
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.
How to repeat:
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.
Suggested fix:
Implement feature to specify not only number of persistent pages, but also to keep them same when calculating statistics for different indexes.
