~drizzle-trunk/drizzle/development

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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
drop table if exists t0, t1, t2, t10, t11, t12;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int, b int);
insert into t1 values (0,0),(1,1),(2,2);
create table t2 as select * from t1;
create table t11(a int, b int);
create table t10 (pk int, a int, primary key(pk));
insert into t10 select a,a from t0;
create table t12 like t10;
insert into t12 select * from t10;
Flattened because of dependency, t10=func(t1)
explain select * from t1 where a in (select pk from t10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
2	SUBQUERY	t10	index	NULL	PRIMARY	4	NULL	10	Using index
select * from t1 where a in (select pk from t10);
a	b
0	0
1	1
2	2
A confluent case of dependency
explain select * from t1 where a in (select a from t10 where pk=12);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
select * from t1 where a in (select a from t10 where pk=12);
a	b
An empty table inside
explain select * from t1 where a in (select a from t11);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
2	SUBQUERY	t11	ALL	NULL	NULL	NULL	NULL	1	
select * from t1 where a in (select a from t11);
a	b
explain select * from t1 where a in (select pk from t10) and b in (select pk from t10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
3	SUBQUERY	t10	index	NULL	PRIMARY	4	NULL	10	Using index
2	SUBQUERY	t10	index	NULL	PRIMARY	4	NULL	10	Using index
select * from t1 where a in (select pk from t10) and b in (select pk from t10);
a	b
0	0
1	1
2	2
flattening a nested subquery
explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
2	SUBQUERY	t10	ALL	NULL	NULL	NULL	NULL	10	Using where
3	SUBQUERY	t12	index	NULL	PRIMARY	4	NULL	10	Using index
select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
a	b
0	0
1	1
2	2
flattening subquery w/ several tables
explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
2	SUBQUERY	t10	ALL	NULL	NULL	NULL	NULL	10	100.00	
2	SUBQUERY	t12	eq_ref	PRIMARY	PRIMARY	4	test.t10.a	1	100.00	Using index
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t10`.`pk` AS `pk` from `test`.`t10` join `test`.`t12` where (`test`.`t12`.`pk` = `test`.`t10`.`a`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key)))
subqueries within outer joins go into ON expr.
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) 
where t1.a < 5;
a	b	a	b
0	0	0	0
1	1	1	1
2	2	2	2
insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B;
explain extended select * from t1 where a in (select pk from t10 where pk<3);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	103	100.00	Using where
2	SUBQUERY	t10	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using where; Using index
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t10`.`pk` AS `pk` from `test`.`t10` where (`test`.`t10`.`pk` < 3) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key)))
drop table t0, t1;
drop table t10, t11, t12;
drop table t2;