Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.9
-
None
Description
filtered% value is incorrect for single-table select and two-keypart key.
create table ten(a int);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
|
create table t2 (a int, b int, col1 varchar(64), col2 varchar(64), key(a,b));
|
insert into t2 select A.a+10*B.a, C.a+10*D.a, 'filler-data1', 'filler-data2' from ten A, ten B, ten C, ten D;
|
|
|
set histogram_size=100;
|
set optimizer_use_condition_selectivity=4;
|
set use_stat_tables='preferably';
|
analyze table t2 persistent for all;
|
Then, try this:
explain extended select * from t2 where a in (1,2,3) and b in (1,2,3);
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
|
| 1 | SIMPLE | t2 | range | a | a | 10 | NULL | 9 | 3.37 | Using index condition |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
|
The entire WHERE clause is used to construct range access. If we use range access, it will give us 9 rows, and after that 'filtered' should be 100%. Instead, it is 3.37%, and again the optimizer will grossly underestimate #rows.