Public bug reported:
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.
** Affects: mysql-server
Importance: Unknown
Status: Unknown
** Affects: percona-server
Importance: Undecided
Status: Confirmed
** Affects: percona-server/5.5
Importance: Undecided
Status: Confirmed
** Affects: percona-server/5.6
Importance: Undecided
Status: Confirmed
** Affects: percona-server/5.7
Importance: Undecided
Status: Confirmed
** Tags: i196728
** Attachment added: "test case for MTR"
https://bugs.launchpad.net/bugs/1715744/+attachment/4946148/+files/i196728_diff_card.test
** Also affects: percona-server/5.6
Importance: Undecided
Status: New
** Also affects: percona-server/5.5
Importance: Undecided
Status: New
** Also affects: percona-server/5.7
Importance: Undecided
Status: Confirmed
** Changed in: percona-server/5.5
Status: New => Confirmed
** Changed in: percona-server/5.6
Status: New => Confirmed
** Bug watch added: MySQL Bug System #87699
http://bugs.mysql.com/bug.php?id=87699
** Also affects: mysql-server via
http://bugs.mysql.com/bug.php?id=87699
Importance: Unknown
Status: Unknown
--
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/1715744
Title:
Different cardinality in different indexes for the same column
To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1715744/+subscriptions
--
Mailing list: https://launchpad.net/~enterprise-support
Post to : [email protected]
Unsubscribe : https://launchpad.net/~enterprise-support
More help : https://help.launchpad.net/ListHelp
