|
The value of innodb_stats_method is not honored when innodb_stats_persistent=ON.
Steps to reproduce:
Let's first try with non-persistent statistics:
set global innodb_stats_persistent=off;
|
set global innodb_stats_transient_sample_pages=100;
|
set global innodb_stats_persistent_sample_pages=100;
|
mysql> show variables like 'innodb%stats%';
|
+--------------------------------------+-------------+
|
| Variable_name | Value |
|
+--------------------------------------+-------------+
|
| innodb_defragment_stats_accuracy | 0 |
|
| innodb_stats_auto_recalc | ON |
|
| innodb_stats_include_delete_marked | OFF |
|
| innodb_stats_method | nulls_equal |
|
| innodb_stats_modified_counter | 0 |
|
| innodb_stats_on_metadata | OFF |
|
| innodb_stats_persistent | OFF |
|
| innodb_stats_persistent_sample_pages | 100 |
|
| innodb_stats_sample_pages | 100 |
|
| innodb_stats_traditional | ON |
|
| innodb_stats_transient_sample_pages | 100 |
|
+--------------------------------------+-------------+
|
create table ten(a int);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
create table one_k(a int);
|
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
|
create table t1 (pk int primary key , a int, key(a)) engine=innodb;
|
# 400 K rows with NULLs:
|
insert into t1 select A.a+1000*B.a, null from one_k A , one_k B where B.a< 400;
|
# 101K rows with different non-NULL values:
|
insert into t1
|
select
|
A.a+1000*B.a,A.a+1000*B.a from one_k A, one_k B
|
where B.a between 400 and 500;
|
analyze table t1;
|
Now, let's see what we've got:
mysql> show keys from t1;
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| t1 | 0 | PRIMARY | 1 | pk | A | 506705 | NULL | NULL | | BTREE | | |
|
| t1 | 1 | a | 1 | a | A | 253352 | NULL | NULL | YES | BTREE | | |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
Cardinality(a)=253K. There are actually 101K different values, but this is an estimate, so I'm fine with this value (for this MDEV at least).
Now, let's treat all NULLs as unequal, which means 'a' will have 500K different values:
set global innodb_stats_method=nulls_unequal;
|
analyze table t1;
|
mysql> show keys from t1;
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| t1 | 0 | PRIMARY | 1 | pk | A | 498951 | NULL | NULL | | BTREE | | |
|
| t1 | 1 | a | 1 | a | A | 498951 | NULL | NULL | YES | BTREE | | |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
Cardinality(a)=498K. Good - same as PK, close to reality.
Now, let's try to repeat the above steps with persistent statistics.
drop table t1;
|
set global innodb_stats_persistent=on;
|
set global innodb_stats_method=nulls_equal;
|
create table t1 (pk int primary key , a int, key(a)) engine=innodb;
|
insert into t1 select A.a+1000*B.a, null from one_k A , one_k B where B.a< 400;
|
insert into t1
|
select
|
A.a+1000*B.a,A.a+1000*B.a from one_k A, one_k B
|
where B.a between 400 and 500;
|
analyze table t1;
|
mysql> show keys from t1;
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| t1 | 0 | PRIMARY | 1 | pk | A | 501337 | NULL | NULL | | BTREE | | |
|
| t1 | 1 | a | 1 | a | A | 250668 | NULL | NULL | YES | BTREE | | |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
Cardinality(a)=250K. Close to what we've got for non-persistent stats, ok.
set global innodb_stats_method=nulls_unequal;
|
analyze table t1;
|
mysql> show keys from t1;
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| t1 | 0 | PRIMARY | 1 | pk | A | 500745 | NULL | NULL | | BTREE | | |
|
| t1 | 1 | a | 1 | a | A | 250372 | NULL | NULL | YES | BTREE | | |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
Still, Cardinality(a)=250K! Now, this is NOT acceptable.
with nulls_unequal, all values of 'a' are different, there is no reason for innodb to produce cardinality(a) < cardinality(PK)!
|
