Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.4(EOL)
-
None
Description
The optimizer may choose poor (expensive) access methods to be used together with LooseScan semi-join strategy.
The original example was observed at a customer, and we don't have access to the dataset.
I've also found this example in subselect3.result. I'm not sure if it is the same issue, but this gives an example to study:
create table t0(a int); |
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2)); |
insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C; |
insert into t1 select * from t1 where kp1 < 20; |
create table t3 (a int); |
insert into t3 select A.a + 10*B.a from t0 A, t0 B; |
explain select * from t3 where a in (select kp1 from t1 where kp1<20);
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
|
| 1 | PRIMARY | t1 | index | kp1 | kp1 | 10 | NULL | 1020 | Using where; Using index; LooseScan |
|
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 100 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
|
Why does LooseScan use type=index? There is a possible range access on index kp1. It is certainly better than the full table scan.
Running the subquery standalone uses the select:
mysql> explain select kp1 from t1 where kp1<20;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | range | kp1 | kp1 | 5 | NULL | 40 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
Attachments
Issue Links
- relates to
-
-
- Closed
-