"Range checked for each record" is not used if condition refers to outside of subquery
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Medium
|
Sergey Petrunia |
Bug Description
"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)
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
Changed in maria: | |
assignee: | nobody → Sergey Petrunia (sergefp) |
importance: | Undecided → Medium |
status: | New → Fix Committed |
milestone: | none → 5.3 |
Changed in maria: | |
status: | Fix Committed → Fix Released |