Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.9
-
None
Description
create table ten(a int);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
create table t1(key1 int, col1 int, key(key1));
|
insert into t1 select A.a, A.a from ten A, ten B, ten C;
|
 |
set histogram_size=100;
|
set use_stat_tables='preferably';
|
set optimizer_use_condition_selectivity=4;
|
analyze table t1 persistent for all;
|
mysql> explain extended select * from t1 where col1=2;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 9.90 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
This is ok.
mysql> explain extended select * from t1 where key1=2;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------+
|
| 1 | SIMPLE | t1 | ref | key1 | key1 | 5 | const | 100 | 10.00 | |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------+
|
This is not ok. "key1=2" is used for ref access. However, its selectivity is still present in "filtered%". Ref access is employed when a condition is very selective, counting its selectivity twice will cause gross under-estimation of output cardinality.