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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
|
drop table if exists t0, t1, t2, t3;
drop view if exists v1, v2;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3);
create table t0 as select * from t1;
create table t2 (a int primary key, b int)
as select a, a as b from t1 where a in (1,2);
create table t3 (a int primary key, b int)
as select a, a as b from t1 where a in (1,3);
# This will be eliminated:
explain select t1.a from t1 left join t2 on t2.a=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
explain extended select t1.a from t1 left join t2 on t2.a=t1.a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
select t1.a from t1 left join t2 on t2.a=t1.a;
a
0
1
2
3
# This will not be eliminated as t2.b is in in select list:
explain select * from t1 left join t2 on t2.a=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
# This will not be eliminated as t2.b is in in order list:
explain select t1.a from t1 left join t2 on t2.a=t1.a order by t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
# This will not be eliminated as t2.b is in group list:
explain select t1.a from t1 left join t2 on t2.a=t1.a group by t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
# This will not be eliminated as t2.b is in the WHERE
explain select t1.a from t1 left join t2 on t2.a=t1.a where t2.b < 3 or t2.b is null;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
# Elimination of multiple tables:
explain select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
# Elimination of multiple tables (2):
explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
# Elimination when done within an outer join nest:
explain extended
select t0.*
from
t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and
t3.a=t1.a) on t0.a=t1.a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t0 ALL NULL NULL NULL NULL 4 100.00
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00
Warnings:
Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on((`test`.`t0`.`a` = `test`.`t1`.`a`)) where 1
# Elimination with aggregate functions
explain select count(*) from t1 left join t2 on t2.a=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
explain select count(1) from t1 left join t2 on t2.a=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
explain select count(1) from t1 left join t2 on t2.a=t1.a group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
This must not use elimination:
explain select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
drop table t0, t1, t2, t3;
create table t0 ( id integer, primary key (id));
create table t1 (
id integer,
attr1 integer,
primary key (id),
key (attr1)
);
create table t2 (
id integer,
attr2 integer,
fromdate date,
primary key (id, fromdate),
key (attr2,fromdate)
);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
insert into t0 select A.id + 10*B.id from t0 A, t0 B where B.id > 0;
insert into t1 select id, id from t0;
insert into t2 select id, id, date_add('2009-06-22', interval id day) from t0;
insert into t2 select id, id+1, date_add('2008-06-22', interval id day) from t0;
create view v1 as
select
F.id, A1.attr1, A2.attr2
from
t0 F
left join t1 A1 on A1.id=F.id
left join t2 A2 on A2.id=F.id and
A2.fromdate=(select MAX(fromdate) from
t2 where id=A2.id);
create view v2 as
select
F.id, A1.attr1, A2.attr2
from
t0 F
left join t1 A1 on A1.id=F.id
left join t2 A2 on A2.id=F.id and
A2.fromdate=(select MAX(fromdate) from
t2 where id=F.id);
This should use one table:
explain select id from v1 where id=2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY F const PRIMARY PRIMARY 4 const 1 Using index
This should use one table:
explain extended select id from v1 where id in (1,2,3,4);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY F range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index
Warnings:
Note 1276 Field or reference 'test.A2.id' of SELECT #3 was resolved in SELECT #1
Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` where (`F`.`id` in (1,2,3,4))
This should use facts and A1 tables:
explain extended select id from v1 where attr1 between 12 and 14;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY A1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using where
1 PRIMARY F eq_ref PRIMARY PRIMARY 4 test.A1.id 1 100.00 Using index
Warnings:
Note 1276 Field or reference 'test.A2.id' of SELECT #3 was resolved in SELECT #1
Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` join `test`.`t1` `A1` where ((`F`.`id` = `A1`.`id`) and (`A1`.`attr1` between 12 and 14))
This should use facts, A2 and its subquery:
explain extended select id from v1 where attr2 between 12 and 14;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY A2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using where
1 PRIMARY F eq_ref PRIMARY PRIMARY 4 test.A2.id 1 100.00 Using index
3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.A2.id 2 100.00 Using index
Warnings:
Note 1276 Field or reference 'test.A2.id' of SELECT #3 was resolved in SELECT #1
Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` join `test`.`t2` `A2` where ((`F`.`id` = `A2`.`id`) and (`A2`.`attr2` between 12 and 14) and (`A2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from `test`.`t2` where (`test`.`t2`.`id` = `A2`.`id`))))
This should use one table:
explain select id from v2 where id=2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY F const PRIMARY PRIMARY 4 const 1 Using index
This should use one table:
explain extended select id from v2 where id in (1,2,3,4);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY F range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index
Warnings:
Note 1276 Field or reference 'test.F.id' of SELECT #3 was resolved in SELECT #1
Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` where (`F`.`id` in (1,2,3,4))
This should use facts and A1 tables:
explain extended select id from v2 where attr1 between 12 and 14;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY A1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using where
1 PRIMARY F eq_ref PRIMARY PRIMARY 4 test.A1.id 1 100.00 Using index
Warnings:
Note 1276 Field or reference 'test.F.id' of SELECT #3 was resolved in SELECT #1
Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` join `test`.`t1` `A1` where ((`F`.`id` = `A1`.`id`) and (`A1`.`attr1` between 12 and 14))
This should use facts, A2 and its subquery:
explain extended select id from v2 where attr2 between 12 and 14;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY A2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using where
1 PRIMARY F eq_ref PRIMARY PRIMARY 4 test.A2.id 1 100.00 Using where; Using index
3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.F.id 2 100.00 Using index
Warnings:
Note 1276 Field or reference 'test.F.id' of SELECT #3 was resolved in SELECT #1
Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` join `test`.`t2` `A2` where ((`F`.`id` = `A2`.`id`) and (`A2`.`attr2` between 12 and 14) and (`A2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from `test`.`t2` where (`test`.`t2`.`id` = `F`.`id`))))
drop view v1, v2;
drop table t0, t1, t2;
|
