Description:
When persistent statistics is used for InnoDB it still updates cardinality with values which are incorrect.
Can be related to bug #78066 (but exists in 5.7.10) and bug #75428
How to repeat:
Option file:
--innodb_stats_auto_recalc=0 --innodb_stats_method=nulls_equal --innodb_stats_on_metadata=0 --innodb_stats_persistent=1 --innodb_stats_persistent_sample_pages=1000 --innodb_stats_sample_pages=8 --innodb_stats_transient_sample_pages=8
Test file:
--source include/have_innodb.inc
create table sale
(
id int primary key auto_increment,
customer_id int not null,
product_id int not null,
sale_time datetime not null,
sale_value decimal(10,2) not null,
filler varchar(250) not null,
key(customer_id, sale_time),
key(product_id)
)
engine innodb STATS_AUTO_RECALC=0 STATS_SAMPLE_PAGES=1000;
insert into sale
(customer_id,product_id,sale_time,sale_value,filler)
values
(1,1,NOW(),100,LPAD('X',250,'X')),
(1,2,NOW(),200,LPAD('X',250,'X')),
(1,3,NOW(),300,LPAD('X',250,'X')),
(2,4,NOW(),100,LPAD('X',250,'X')),
(2,5,NOW(),200,LPAD('X',250,'X')),
(3,6,NOW(),100,LPAD('X',250,'X'));
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale;
show indexes from sale;
select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale;
select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale;
analyze table sale;
show indexes from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale where id < 441/5;
show indexes from sale;
select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale;
select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale where id < 581/2;
show indexes from sale;
select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale;
select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale;
Actual result:
show indexes from sale;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sale 0 PRIMARY 1 id A 128 NULL NULL BTREE
sale 1 customer_id 1 customer_id A 128 NULL NULL BTREE
sale 1 customer_id 2 sale_time A 128 NULL NULL BTREE
sale 1 product_id 1 product_id A 128 NULL NULL BTREE
select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale;
count(distinct customer_id) count(distinct sale_time) count(distinct product_id)
3 2 6
select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale;
count(*) count(distinct customer_id) count(distinct customer_id, sale_time) count(distinct product_id)
384 3 6 6
analyze table sale;
Table Op Msg_type Msg_text
test.sale analyze status OK
show indexes from sale;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sale 0 PRIMARY 1 id A 384 NULL NULL BTREE
sale 1 customer_id 1 customer_id A 6 NULL NULL BTREE
sale 1 customer_id 2 sale_time A 12 NULL NULL BTREE
sale 1 product_id 1 product_id A 12 NULL NULL BTREE
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale where id < 441/5;
show indexes from sale;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sale 0 PRIMARY 1 id A 461 NULL NULL BTREE
sale 1 customer_id 1 customer_id A 7 NULL NULL BTREE
sale 1 customer_id 2 sale_time A 14 NULL NULL BTREE
sale 1 product_id 1 product_id A 14 NULL NULL BTREE
select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale;
count(distinct customer_id) count(distinct sale_time) count(distinct product_id)
3 3 6
select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale;
count(*) count(distinct customer_id) count(distinct customer_id, sale_time) count(distinct product_id)
461 3 9 6
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale where id < 581/2;
show indexes from sale;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sale 0 PRIMARY 1 id A 694 NULL NULL BTREE
sale 1 customer_id 1 customer_id A 10 NULL NULL BTREE
sale 1 customer_id 2 sale_time A 21 NULL NULL BTREE
sale 1 product_id 1 product_id A 21 NULL NULL BTREE
select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale;
count(distinct customer_id) count(distinct sale_time) count(distinct product_id)
3 4 6
select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale;
count(*) count(distinct customer_id) count(distinct customer_id, sale_time) count(distinct product_id)
694 3 12 6
Suggested fix:
all calls of show indexes from sale; (except first one) should return
show indexes from sale;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sale 0 PRIMARY 1 id A 384 NULL NULL BTREE
sale 1 customer_id 1 customer_id A 6 NULL NULL BTREE
sale 1 customer_id 2 sale_time A 12 NULL NULL BTREE
sale 1 product_id 1 product_id A 12 NULL NULL BTREE