Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
Description
This is the range access part of MDEV-17493.
create table t2(a int); |
insert into t2 values (0),(1); |
create table t1 (a int, b int, key(a)); |
insert into t1 select A.a + B.a* 10 + C.a * 100, 12345 from ten A, ten B, ten C; |
Outer join with single-table on the inner side is able to use the ON expression to construct range access:
mysql> explain select * from ten left join t1 on ten.a=t1.b and t1.a<5;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
|
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 5 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
|
add another table there and range access is not used anymore:
mysql> explain select * from ten left join (t1 join t2) on ten.a=t1.b and t1.a<5;
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 1000 | Using where; Using join buffer (incremental, BNL join) |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
Attachments
Issue Links
- relates to
-
-
- Closed
-