Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
None
-
None
-
None
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),(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