Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.11
-
None
Description
The optimizer can't reorder tables for OUTER JOINs, but when a later WHERE condition on a column of an outer table requires a non-NULL value it can convert the OUTER join into an INNER one, and then it can also make use of re-ordering.
The same works when the OUTER JOIN is inside a view definition, and a WHERE condition on a column of the outer table is applied on the view ... but apparently only if the view definition doesn't have a WHERE clause.
How to reproduce:
DROP TABLE IF EXISTS t1;
|
|
|
CREATE TABLE t1(i1 int primary key, v1 int, key(v1)) engine=myisam;
|
|
|
INSERT INTO t1 VALUES (1, 1);
|
INSERT INTO t1 VALUES (2, 2);
|
INSERT INTO t1 VALUES (3, 3);
|
INSERT INTO t1 VALUES (4, 4);
|
INSERT INTO t1 VALUES (5, 3);
|
INSERT INTO t1 VALUES (6, 6);
|
INSERT INTO t1 VALUES (7, 7);
|
INSERT INTO t1 VALUES (8, 8);
|
INSERT INTO t1 VALUES (9, 9);
|
|
|
DROP TABLE IF EXISTS t2;
|
|
|
CREATE TABLE t2(i2 int primary key, v2 int, key(v2)) engine=myisam;
|
|
|
INSERT INTO t2 VALUES (1, 1);
|
INSERT INTO t2 VALUES (2, 2);
|
INSERT INTO t2 VALUES (3, 3);
|
INSERT INTO t2 VALUES (4, 4);
|
INSERT INTO t2 VALUES (5, 3);
|
INSERT INTO t2 VALUES (6, 6);
|
INSERT INTO t2 VALUES (7, 7);
|
INSERT INTO t2 VALUES (8, 8);
|
INSERT INTO t2 VALUES (9, 9);
|
|
|
DROP TABLE IF EXISTS t3;
|
|
|
CREATE TABLE t3(i3 int primary key, v3 int, key(v3)) engine=myisam;
|
|
|
INSERT INTO t3 VALUES (2, 2);
|
INSERT INTO t3 VALUES (4, 4);
|
INSERT INTO t3 VALUES (6, 6);
|
INSERT INTO t3 VALUES (8, 8);
|
|
|
# view definition without WHERE
|
|
|
DROP VIEW IF EXISTS v1;
|
|
|
CREATE ALGORITHM=MERGE VIEW v1 AS
|
SELECT t1.i1 as i1, t1.v1 as v1,
|
t2.i2 as i2, t2.v2 as v2,
|
t3.i3 as i3, t3.v3 as v3
|
FROM t1
|
JOIN t2 on t1.i1 = t2.i2
|
LEFT JOIN t3 on t2.i2 = t3.i3
|
;
|
|
|
# view definition with WHERE
|
|
|
DROP VIEW IF EXISTS v2;
|
|
|
CREATE ALGORITHM=MERGE VIEW v2 AS
|
SELECT t1.i1 as i1, t1.v1 as v1,
|
t2.i2 as i2, t2.v2 as v2,
|
t3.i3 as i3, t3.v3 as v3
|
FROM t1 JOIN t2 on t1.i1 = t2.i2
|
LEFT JOIN t3 on t2.i2 = t3.i3
|
WHERE t1.i1 = t2.i2
|
AND 1 = 1
|
;
|
|
|
# query plan of plain query
|
|
|
EXPLAIN EXTENDED SELECT t1.i1 as i1, t1.v1 as v1,
|
t2.i2 as i2, t2.v2 as v2,
|
t3.i3 as i3, t3.v3 as v3
|
FROM t1
|
JOIN t2 on t1.i1 = t2.i2
|
LEFT JOIN t3 on t2.i2 = t3.i3
|
WHERE 1 = 1
|
AND t3.v3 = 4
|
;
|
|
|
# query plan for view without WHERE
|
|
|
EXPLAIN EXTENDED SELECT * FROM v1 WHERE v3 = 4;
|
|
|
# query plan for view with WHERE
|
|
|
EXPLAIN EXTENDED SELECT * FROM v2 WHERE v3 = 4;
|
The plans for the plain query, and for the VIEW without a WHERE condition inside the definition, look like this:
Plain query:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t3 ref PRIMARY,v3 v3 5 const 1 100.00
|
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.i3 1 100.00
|
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.i3 1 100.00
|
View without WHERE:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t3 ref PRIMARY,v3 v3 5 const 1 100.00
|
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.i3 1 100.00
|
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.i3 1 100.00
|
So these two are identical.
For the VIEW with the extra WHERE 1=1 in its definition the plan looks like this though:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 9 100.00
|
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1 100.00
|
1 SIMPLE t3 eq_ref PRIMARY,v3 PRIMARY 4 test.t1.i1 1 100.00 Using where
|
So here table reordering didn't happen, and a "Using where" full scan of the VIEW results has to be done ....