|
"Range checked for each record" optimization is not used if condition refers to outside of the subquery.
create table ten (a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1big (a int, b int, `filler` char(200), key(a), key (b));
insert into t1big select A.a + 10*B.a + 100 * C.a, A.a + 10*B.a + 100 * C.a, 'filler' from ten A, ten B, ten C;
MariaDB [test]> explain select a, (select sum(X.a+B.b) from ten X, t1big B where B.a=A.a or B.b=A.a) from ten A;
---
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
---
| 1 |
PRIMARY |
A |
ALL |
NULL |
NULL |
NULL |
NULL |
10 |
|
| 2 |
DEPENDENT SUBQUERY |
X |
ALL |
NULL |
NULL |
NULL |
NULL |
10 |
|
| 2 |
DEPENDENT SUBQUERY |
B |
ALL |
a,b |
NULL |
NULL |
NULL |
1000 |
Using where; Using join buffer (flat, BNL join) |
---
The above query could have been much more efficient if access to table B was done with "Range checked for each record" over the condition of "B.a=A.a or B.b=A.a" (every time one would be able to find an index_merge plan that only scans two records).
The problem can be observed on MariaDB 5.x as well as MySQL 5.x
|
LPexportBug716293.xml