1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
CREATE TABLE t1 (
col_int_key integer,
col_varchar varchar(1),
col_varchar_key varchar(1),
KEY k1 (col_int_key),
KEY k2 (col_varchar_key)
);
INSERT INTO t1 VALUES (1,'f','5'),(2,'H','f'),(3,'D','u');
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
CREATE TABLE t2 (
col_int_key integer,
col_varchar varchar(1),
col_varchar_key varchar(1),
KEY k3 (col_int_key),
KEY k4 (col_varchar_key)
);
INSERT INTO t2 VALUES (4,'w','c');
CREATE TABLE a (
f1 varchar(1),
KEY k5 (f1)
);
CREATE VIEW v1 AS SELECT f1 from a;
EXPLAIN FORMAT=tree SELECT col_varchar_key FROM t1
WHERE ( col_varchar_key, col_varchar_key ) NOT IN (
SELECT alias1.col_varchar_key, alias1.col_varchar_key
FROM (
t1 AS alias1
JOIN ( t1 AS alias2 JOIN t2 ON t2.col_varchar_key = alias2.col_varchar_key )
ON ( t2.col_int_key = alias2.col_int_key AND alias2.col_varchar_key IN ( SELECT f1 FROM v1 ) ) )
WHERE alias1.col_varchar >= 'Z' );
EXPLAIN
-> Filter: <in_optimizer>((t1.col_varchar_key,t1.col_varchar_key),<exists>(select #2) is false) (cost=0.55 rows=3)
-> Index scan on t1 using k2 (cost=0.55 rows=3)
-> Select #2 (subquery in condition; dependent)
-> Limit: 1 row(s) (cost=0.622 rows=0.222)
-> Filter: (<if>(outer_field_is_not_null, <is_not_null_test>(alias1.col_varchar_key), true) and <if>(outer_field_is_not_null, <is_not_null_test>(alias1.col_varchar_key), true)) (cost=0.622 rows=0.222)
-> Nested loop inner join (cost=0.622 rows=0.222)
-> Filter: (t2.col_varchar_key is not null) (cost=0.35 rows=1)
-> Index range scan on t2 using k3 over (NULL < col_int_key), with index condition: (t2.col_int_key is not null) (cost=0.35 rows=1)
-> Remove duplicate (alias2, alias1) rows using temporary table (weedout) (cost=0.933 rows=0.222)
-> Nested loop inner join (cost=0.933 rows=0.222)
-> Nested loop inner join (cost=0.7 rows=0.333)
-> Covering index lookup on a using k5 (f1=t2.col_varchar_key) (cost=0.35 rows=1)
-> Filter: (alias2.col_varchar_key = t2.col_varchar_key) (cost=0.283 rows=0.333)
-> Index lookup on alias2 using k1 (col_int_key=t2.col_int_key) (cost=0.283 rows=1)
-> Filter: ((alias1.col_varchar >= 'Z') and <if>(outer_field_is_not_null, ((<cache>(t1.col_varchar_key) = alias1.col_varchar_key) or (alias1.col_varchar_key is null)), true) and <if>(outer_field_is_not_null, ((<cache>(t1.col_varchar_key) = alias1.col_varchar_key) or (alias1.col_varchar_key is null)), true)) (cost=0.7 rows=0.667)
-> Alternative plans for IN subquery: Index lookup unless col_varchar_key IS NULL (cost=0.7 rows=2)
-> Index lookup on alias1 using k2 (col_varchar_key=<cache>(t1.col_varchar_key) or NULL) (cost=0.7 rows=2)
-> Table scan on alias1 (cost=0.25 rows=3)
SELECT col_varchar_key FROM t1
WHERE ( col_varchar_key, col_varchar_key ) NOT IN (
SELECT alias1.col_varchar_key, alias1.col_varchar_key
FROM (
t1 AS alias1
JOIN ( t1 AS alias2 JOIN t2 ON t2.col_varchar_key = alias2.col_varchar_key )
ON ( t2.col_int_key = alias2.col_int_key AND alias2.col_varchar_key IN ( SELECT f1 FROM v1 ) ) )
WHERE alias1.col_varchar >= 'Z' );
col_varchar_key
5
f
u
DROP VIEW v1;
DROP TABLE t1, t2, a;
|
