Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
11.4
-
None
-
Not for Release Notes
Description
(discovered this while looking at MDEV-35280)
Let's try to use Selectivity Sampling feature (optimizer_use_condition_selectivity=5).
create table t1 (a varchar(100), b int); |
insert into t1 select seq, seq from seq_1_to_10000; |
# No need for ANALYZE TABLE. |
set optimizer_use_condition_selectivity=5; |
explain extended
|
select * from t1 where a like '%99%';
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10000 | 100.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
filtered=100, it didn't work?
Let's add an irrelevant condition on an indexed column
(range optimizer is able to infer that b is null or b is not null doesn't produce any ranges):
explain extended
|
select * from t1 where a like '%99%' and (b is null or b is not null);
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10000 | 1.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
Now, filtered=1%.
Optimizer Trace shows this:
"rows_estimation": [
|
{
|
"selectivity_for_indexes": [],
|
"selectivity_for_columns": [],
|
"cond_selectivity": 0.01
|
},
|
{
|
"table": "t1",
|
"table_scan": {
|
"rows": 10330,
|
"read_cost": 1.3846,
|
"read_and_compare_cost": 1.71516
|
}
|
}
|
]
|
Doesn't say explicitly it's from sampling, but selectivity_for_indexes and selectivity_for_columns are empty...
Attachments
Issue Links
- duplicates
-
-
- In Progress
-