Details
Blocker Description
Consider a testcase:
create table t1 (a int); |
insert into t1 select seq from seq_1_to_100; |
 |
create table t2 ( |
kp1 int, |
kp2 int, |
filler char(100), |
key(kp1, kp2) |
);
|
insert into t2 |
select |
seq, seq,
|
'filler-data' |
from seq_1_to_10000; |
Suppose table t2 has
- a ref(non-const) access
- a range access that uses more keyparts than ref:
Query Q1:
explain
|
select * |
from t1, t2 |
where |
t2.kp1=t1.a and t2.kp1<=100 and t2.kp2<=20 |
The query plan is:
+------+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
|
| 1 | SIMPLE | t2 | range | kp1 | kp1 | 10 | NULL | 999 | Using index condition; Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------------------------------+
|
One can get a much better if one prevents the optimizer from constructing range access with two keyparts:
Query Q2:
explain
|
select * |
from t1, t2 |
where
|
t2.kp1=t1.a and t2.kp1<=1000 and t2.kp2+1<=20; -- make kp2 unusable for range. |
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
|
| 1 | SIMPLE | t2 | ref | kp1 | kp1 | 5 | test.t1.a | 1 | Using index condition |
|
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------+
|
Examining the optimizer trace for Q1, one can see that the optimizer picks ref access for table t2, but then the rewrite makes it range access:
"attached_conditions_computation": [
|
{
|
"ref_to_range": true,
|
"cause": "range uses longer key"
|
}
|
],
|