|
This is based on observations over MDEV-4362 and MDEV-4364.
Populate the table with 100 rows with a=0, 100 rows with a=1, etc. (the data distribution is perfectly uniform)
|
create table ten(a int);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
create table t1(a int);
|
insert into t1 select A.a from ten A, ten B, ten C;
|
set histogram_size=20;
|
set histogram_type='single_prec_hb';
|
analyze table t1 persistent for all;
|
set use_stat_tables='preferably';
|
set optimizer_use_condition_selectivity=4;
|
mysql> explain extended select * from t1 where a=2;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 8.67 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
Not a precise estimate, but it is probably ok.
mysql> explain extended select * from t1 where a=1;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 8.67 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
OK.
mysql> explain extended select * from t1 where a=0;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
Boom. The number of rows with a=0 is 10% of the table. Not ok.
mysql> explain extended select * from t1 where a=-1;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
-1 is not present at all. Not ok.
|