|
CREATE TABLE t1 ( pk int NOT NULL PRIMARY KEY, ci1 int, ci2 int, v1 varchar(1), v2 varchar(1));
|
|
INSERT INTO t1 VALUES (1,2,4,'v','v'), (2,150,62,'v','v'), (3,NULL,7,'c','c'), (4,2,1,NULL,NULL);
|
INSERT INTO t1 VALUES (5,5,0,'x','x'), (6,3,7,'i','i'), (7,1,7,'e','e'), (8,4,1,'p','p');
|
|
CREATE TABLE t2 (pk int NOT NULL PRIMARY KEY, ci1 int, ci2 int, v1 varchar(1), v2 varchar(1),
|
KEY ci2 (ci2));
|
|
INSERT INTO t2 VALUES (10,NULL,8,'x','x'), (11,8,7,'d','d'), (12,1,1,'r','r'), (13,9,7,'f','f');
|
INSERT INTO t2 VALUES (14,4,9,'y','y'), (15,3,NULL,'u','u'), (16,2,1,'m','m'), (17,NULL,9,NULL,NULL);
|
INSERT INTO t2 VALUES (18,2,2,'o','o'), (19,NULL,9,'w','w'), (20,6,2,'m','m'), (21,7,4,'q','q');
|
INSERT INTO t2 VALUES (22,2,0,NULL,NULL), (23,5,4,'d','d'), (24,7,8,'g','g'), (25,6,NULL,'x','x');
|
|
CREATE TABLE t3 (pk int NOT NULL PRIMARY KEY, ci1 int, ci2 int, v1 varchar(1), v2 varchar(1));
|
INSERT INTO t3 VALUES (1,8,4,'c','c'), (2,3,5,'c','c'), (3,3,8,'q','q'), (4,NULL,4,'g','g');
|
INSERT INTO t3 VALUES (5,7,8,'e','e'), (6,4,2,'l','l'), (7,7,9,NULL,NULL), (8,7,6,'v','v');
|
INSERT INTO t3 VALUES (9,8,NULL,'c','c'), (10,6,NULL,'u','u'), (11,3,48,'x','x'), (12,210,228,'x','x');
|
INSERT INTO t3 VALUES (13,1,3,'x','x'), (14,2,5,'l','l'), (15,251,39,'e','e'), (16,4,6,'s','s');
|
INSERT INTO t3 VALUES (17,4,8,'k','k'), (18,9,3,'m','m'), (19,4,NULL,'x','x'), (20,NULL,2,'s','s');
|
INSERT INTO t3 VALUES (21,4,6,'h','h'), (22,NULL,3,'u','u'), (23,1,1,'x','x'), (24,6,4,'l','l');
|
|
SELECT a2.ci2 FROM (t1, (SELECT t3.* FROM ( t3 JOIN t2 ON (t2.v2 = t3.v1 )))AS a2 )
|
WHERE (( a2.ci2, t1.ci2 ) IN
|
( SELECT t2.ci2, MAX(t2.ci1) FROM ( t2 JOIN t3 ON (t3.pk = t2.ci2 )) WHERE ( t3.v2 <> 'w' OR t3.pk < 6 ))) ;
|
|
SET SESSION optimizer_switch='materialization=off,in_to_exists=on';
|
|
SELECT a2.ci2 FROM (t1, (SELECT t3.* FROM ( t3 JOIN t2 ON (t2.v2 = t3.v1 )))AS a2 )
|
WHERE (( a2.ci2, t1.ci2 ) IN
|
( SELECT t2.ci2, MAX(t2.ci1) FROM ( t2 JOIN t3 ON (t3.pk = t2.ci2 )) WHERE ( t3.v2 <> 'w' OR t3.pk < 6 ))) ;
|
|
DROP TABLE t1,t2,t3;
|
MariaDB [test]> SELECT a2.ci2 FROM (t1, (SELECT t3.* FROM ( t3 JOIN t2 ON (t2.v2 = t3.v1 )))AS a2 )
|
-> WHERE (( a2.ci2, t1.ci2 ) IN
|
-> ( SELECT t2.ci2, MAX(t2.ci1) FROM ( t2 JOIN t3 ON (t3.pk = t2.ci2 )) WHERE ( t3.v2 <> 'w' OR t3.pk < 6 ))) ;
|
+------+
|
| ci2 |
|
+------+
|
| 8 |
|
| 8 |
|
| 8 |
|
+------+
|
3 rows in set (0.01 sec)
|
|
MariaDB [test]> explain extended
|
-> SELECT a2.ci2 FROM (t1, (SELECT t3.* FROM ( t3 JOIN t2 ON (t2.v2 = t3.v1 )))AS a2 )
|
-> WHERE (( a2.ci2, t1.ci2 ) IN
|
-> ( SELECT t2.ci2, MAX(t2.ci1) FROM ( t2 JOIN t3 ON (t3.pk = t2.ci2 )) WHERE ( t3.v2 <> 'w' OR t3.pk < 6 ))) ;
|
+------+--------------+-------------+--------+---------------+---------+---------+-------------+------+----------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------+-------------+--------+---------------+---------+---------+-------------+------+----------+--------------------------------------------------------+
|
| 1 | PRIMARY | <subquery3> | ALL | distinct_key | NULL | NULL | NULL | 1 | 100.00 | |
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 16 | 100.00 | Using join buffer (incremental, BNL join) |
|
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 24 | 100.00 | Using where; Using join buffer (incremental, BNL join) |
|
| 3 | MATERIALIZED | t2 | ALL | ci2 | NULL | NULL | NULL | 16 | 100.00 | Using where |
|
| 3 | MATERIALIZED | t3 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.ci2 | 1 | 100.00 | Using where |
|
+------+--------------+-------------+--------+---------------+---------+---------+-------------+------+----------+--------------------------------------------------------+
|
MariaDB [test]> SET SESSION optimizer_switch='materialization=off,in_to_exists=on';
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> SELECT a2.ci2 FROM (t1, (SELECT t3.* FROM ( t3 JOIN t2 ON (t2.v2 = t3.v1 )))AS a2 )
|
-> WHERE (( a2.ci2, t1.ci2 ) IN
|
-> ( SELECT t2.ci2, MAX(t2.ci1) FROM ( t2 JOIN t3 ON (t3.pk = t2.ci2 )) WHERE ( t3.v2 <> 'w' OR t3.pk < 6 ))) ;
|
+------+
|
| ci2 |
|
+------+
|
| 1 |
|
| 1 |
|
| 1 |
|
| 1 |
|
| 1 |
|
| 1 |
|
+------+
|
6 rows in set (0.01 sec)
|
|
MariaDB [test]> explain extended
|
-> SELECT a2.ci2 FROM (t1, (SELECT t3.* FROM ( t3 JOIN t2 ON (t2.v2 = t3.v1 )))AS a2 )
|
-> WHERE (( a2.ci2, t1.ci2 ) IN
|
-> ( SELECT t2.ci2, MAX(t2.ci1) FROM ( t2 JOIN t3 ON (t3.pk = t2.ci2 )) WHERE ( t3.v2 <> 'w' OR t3.pk < 6 ))) ;
|
+------+--------------------+-------+--------+---------------+---------+---------+-------------+------+----------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+-------+--------+---------------+---------+---------+-------------+------+----------+--------------------------------------------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | |
|
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 16 | 100.00 | Using join buffer (flat, BNL join) |
|
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 24 | 100.00 | Using where; Using join buffer (incremental, BNL join) |
|
| 3 | DEPENDENT SUBQUERY | t2 | range | ci2 | ci2 | 5 | NULL | 14 | 100.00 | Using index condition |
|
| 3 | DEPENDENT SUBQUERY | t3 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.ci2 | 1 | 100.00 | Using where |
|
+------+--------------------+-------+--------+---------------+---------+---------+-------------+------+----------+--------------------------------------------------------+
|
|