|
Here is the dataset
create table t0
|
(
|
key1 int not null,
|
INDEX i1(key1)
|
);
|
|
insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
|
|
let $1=7;
|
set @d=8;
|
while ($1)
|
{
|
eval insert into t0 select key1+@d from t0;
|
eval set @d=@d*2;
|
dec $1;
|
}
|
|
alter table t0 add key2 int not null, add index i2(key2);
|
analyze format=json select * from (select * from t0 where key1 = 3 or key2 =3) as Z ;
|
ANALYZE
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.5064,
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 1024,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0103,
|
"filtered": 100,
|
"r_filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 1,
|
"r_total_time_ms": 0.4476,
|
"table": {
|
"table_name": "t0",
|
"access_type": "index_merge",
|
"possible_keys": ["i1", "i2"],
|
"key_length": "4,4",
|
"index_merge": {
|
"union": {
|
"range": {
|
"key": "i1",
|
"used_key_parts": ["key1"]
|
},
|
"range": {
|
"key": "i2",
|
"used_key_parts": ["key2"]
|
}
|
}
|
},
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0096,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "t0.key1 = 3 or t0.key2 = 3"
|
}
|
}
|
}
|
}
|
}
|
}
|
|
alter table t0 add key3 int not null, add index i3(key3);
alter table t0 add key8 int not null, add index i8(key8);
update t0 set key2=key1,key3=key1,key8=1024-key1;
analyze table t0;
set optimizer_switch='derived_merge=off,derived_with_keys=off';
set optimizer_use_condition_selectivity=2;
analyze format=json select * from (select * from t0 where key1 = 3 or key2 =3) as Z ;
|
ANALYZE
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.5064,
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 1024,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0103,
|
"filtered": 100,
|
"r_filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 1,
|
"r_total_time_ms": 0.4476,
|
"table": {
|
"table_name": "t0",
|
"access_type": "index_merge",
|
"possible_keys": ["i1", "i2"],
|
"key_length": "4,4",
|
"index_merge": {
|
"union": {
|
"range": {
|
"key": "i1",
|
"used_key_parts": ["key1"]
|
},
|
"range": {
|
"key": "i2",
|
"used_key_parts": ["key2"]
|
}
|
}
|
},
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0096,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "t0.key1 = 3 or t0.key2 = 3"
|
}
|
}
|
}
|
}
|
}
|
}
|
set optimizer_use_condition_selectivity=1;
analyze format=json select * from (select * from t0 where key1 = 3 or key2 =3) as Z ;
|
ANALYZE
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.3456,
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0092,
|
"filtered": 100,
|
"r_filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 1,
|
"r_total_time_ms": 0.3075,
|
"table": {
|
"table_name": "t0",
|
"access_type": "index_merge",
|
"possible_keys": ["i1", "i2"],
|
"key_length": "4,4",
|
"index_merge": {
|
"union": {
|
"range": {
|
"key": "i1",
|
"used_key_parts": ["key1"]
|
},
|
"range": {
|
"key": "i2",
|
"used_key_parts": ["key2"]
|
}
|
}
|
},
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0089,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "t0.key1 = 3 or t0.key2 = 3"
|
}
|
}
|
}
|
}
|
}
|
}
|
The issue we see is when we have optimizer_use_condition_selectivity set to 2 the derived table has rows =1024 but index merge predicts that there would be 2 rows but looks that during calculation of selectivity we don't take into consideration the rows predicited by index merge
|