enterprise-support team mailing list archive

[Sveta Smirnova <sveta@xxxxxxxxxxxxx>]

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