Description:
"Range checked for each record" optimization is not used for conditions with outer query references, while it can be easily enabled for such cases.
How to repeat:
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int);
insert into t1 values (1),(2);
create table t3 (a int, b int, filler char(100), key(a), key(b));
insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C;
insert into t3 values (1,1,'data');
# Try a query with outer reference
mysql> explain select * from t1 where exists (select 1 from t3 where t3.a< t1.a);
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: t3
type: index
possible_keys: a
key: a
key_len: 5
ref: NULL
rows: 1001
Extra: Using where; Using index
2 rows in set (0.02 sec)
# Now change the outer reference into a local one:
mysql> explain select * from t1 where exists (select 1 from t3, t1 X where X.a=t1.a and t3.a< X.a);
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: X
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
*************************** 3. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: t3
type: ALL
possible_keys: a
key: NULL
key_len: NULL
ref: NULL
rows: 1001
Extra: Range checked for each record (index map: 0x1)
3 rows in set (0.02 sec)
Suggested fix:
Make range-checked-for-each-record handle outer references.
Description: "Range checked for each record" optimization is not used for conditions with outer query references, while it can be easily enabled for such cases. How to repeat: create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int); insert into t1 values (1),(2); create table t3 (a int, b int, filler char(100), key(a), key(b)); insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C; insert into t3 values (1,1,'data'); # Try a query with outer reference mysql> explain select * from t1 where exists (select 1 from t3 where t3.a< t1.a); *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t3 type: index possible_keys: a key: a key_len: 5 ref: NULL rows: 1001 Extra: Using where; Using index 2 rows in set (0.02 sec) # Now change the outer reference into a local one: mysql> explain select * from t1 where exists (select 1 from t3, t1 X where X.a=t1.a and t3.a< X.a); *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: X type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where *************************** 3. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t3 type: ALL possible_keys: a key: NULL key_len: NULL ref: NULL rows: 1001 Extra: Range checked for each record (index map: 0x1) 3 rows in set (0.02 sec) Suggested fix: Make range-checked-for-each-record handle outer references.