Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4, 12.2
Description
(Note that this works in 10.11.16 and doesn't work in 11.4)
create table t1 ( |
col1 int not null, |
col2 int not null, |
primary key(col1, col2) |
);
|
insert into t1 select |
A.seq,
|
B.seq
|
from
|
seq_1_to_100 A,
|
seq_1_to_1000 B;
|
analyze table t1; |
A query that should use Loose Index Scan:
explain select distinct col1 from t1; |
gives
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------+
|
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 8 | NULL | 110510 | |
|
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------+
|
Now let's try the same with non-clustered index:
create table t2 ( |
col1 int not null, |
col2 int not null, |
index(col1, col2) |
);
|
insert into t2 select |
A.seq,
|
B.seq
|
from
|
seq_1_to_100 A,
|
seq_1_to_1000 B;
|
analyze table t2; |
explain select distinct col1 from t2;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t2 | range | NULL | col1 | 4 | NULL | 102 | Using index for group-by |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
Optimizer Trace shows why loose scan is not used for t1:
"rows_estimation": [ |
{
|
"table": "t1", |
"range_analysis": { |
"table_scan": { |
"rows": 110510, |
"cost": 18.3248744 |
},
|
"potential_range_indexes": [ |
{
|
"index": "PRIMARY", |
"usable": true, |
"key_parts": ["col1", "col2"] |
}
|
],
|
"group_index_range": { |
"distinct_query": true, |
"potential_group_range_indexes": [ |
{
|
"index": "PRIMARY", |
"usable": false, |
"cause": "not covering" |
}
|
]
|
Attachments
Issue Links
- relates to
-
MDEV-16402 Support Index Condition Pushdown for clustered PK scans
-
- Confirmed
-