« back to all changes in this revision
Viewing changes to tests/r/pbxt/group_min_max.result
- browse files at revision 1455.3.1
- compare with another revision
- compare with revision 1751.4.24
- download diff
- download tarball
- view history from revision 1455.3.1
- Committer: Vladimir Kolesnikov
- Date: 2010-04-08 16:11:29 UTC
- mto: (1457.1.1 bad-staging) (1455.3.13 drizzle-pbxt-6)
- mto: This revision was merged to the branch mainline in revision 1462.
- Revision ID: vladimir@primebase.org-20100408161129-x85uvbu9qavjck66
lp:drizzle + pbxt 1.1 + test results
- files added:
- plugin/pbxt
- plugin/pbxt/bin
- plugin/pbxt/src
- tests/r/pbxt
- files modified:
- drizzled/sql_table.cc
added
removed
tests/r/pbxt/group_min_max.result
1
drop table if exists t1;
2
create TEMPORARY table t1 (
3
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
4
) ENGINE=MYISAM;
5
insert into t1 (a1, a2, b, c, d) values
6
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
7
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
8
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
9
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
10
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
11
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
12
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
13
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
14
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
15
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
16
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
17
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
18
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
19
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
20
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
21
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
22
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
23
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
24
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
25
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
26
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
27
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
28
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
29
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
30
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
31
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
32
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
33
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
34
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
35
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
36
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
37
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
38
create index idx_t1_0 on t1 (a1);
39
create index idx_t1_1 on t1 (a1,a2,b,c);
40
create index idx_t1_2 on t1 (a1,a2,b);
41
analyze table t1;
42
Table Op Msg_type Msg_text
43
test.t1 analyze note The storage engine for the table doesn't support analyze
44
drop table if exists t2;
45
create TEMPORARY table t2 (
46
a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' '
47
) ENGINE=MYISAM;
48
insert into t2 select * from t1;
49
insert into t2 (a1, a2, b, c, d) values
50
('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
51
('a','a','a',NULL,'xyz'),
52
('a','a','b',NULL,'xyz'),
53
('a','b','a',NULL,'xyz'),
54
('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
55
('d','b','b',NULL,'xyz'),
56
('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
57
('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),
58
('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
59
('a','a','a',NULL,'xyz'),
60
('a','a','b',NULL,'xyz'),
61
('a','b','a',NULL,'xyz'),
62
('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
63
('d','b','b',NULL,'xyz'),
64
('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
65
('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz');
66
create index idx_t2_0 on t2 (a1);
67
create index idx_t2_1 on t2 (a1,a2,b,c);
68
create index idx_t2_2 on t2 (a1,a2,b);
69
analyze table t2;
70
Table Op Msg_type Msg_text
71
test.t2 analyze note The storage engine for the table doesn't support analyze
72
drop table if exists t3;
73
create TEMPORARY table t3 (
74
a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' '
75
) ENGINE=MYISAM;
76
insert into t3 (a1, a2, b, c, d) values
77
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
78
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
79
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
80
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
81
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
82
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
83
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
84
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
85
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
86
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
87
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
88
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
89
insert into t3 (a1, a2, b, c, d) values
90
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
91
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
92
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
93
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
94
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
95
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
96
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
97
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
98
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
99
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
100
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
101
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
102
insert into t3 (a1, a2, b, c, d) values
103
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
104
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
105
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
106
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
107
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
108
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
109
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
110
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
111
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
112
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
113
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
114
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
115
insert into t3 (a1, a2, b, c, d) values
116
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
117
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
118
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
119
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
120
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
121
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
122
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
123
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
124
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
125
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
126
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
127
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
128
create index idx_t3_0 on t3 (a1);
129
create index idx_t3_1 on t3 (a1,a2,b,c);
130
create index idx_t3_2 on t3 (a1,a2,b);
131
analyze table t3;
132
Table Op Msg_type Msg_text
133
test.t3 analyze note The storage engine for the table doesn't support analyze
134
explain select a1, min(a2) from t1 group by a1;
135
id select_type table type possible_keys key key_len ref rows Extra
136
1 SIMPLE t1 range NULL idx_t1_1 518 NULL 5 Using index for group-by
137
explain select a1, max(a2) from t1 group by a1;
138
id select_type table type possible_keys key key_len ref rows Extra
139
1 SIMPLE t1 range NULL idx_t1_1 259 NULL 5 Using index for group-by
140
explain select a1, min(a2), max(a2) from t1 group by a1;
141
id select_type table type possible_keys key key_len ref rows Extra
142
1 SIMPLE t1 range NULL idx_t1_1 518 NULL 5 Using index for group-by
143
explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
144
id select_type table type possible_keys key key_len ref rows Extra
145
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using index for group-by
146
explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
147
id select_type table type possible_keys key key_len ref rows Extra
148
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using index for group-by
149
explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
150
id select_type table type possible_keys key key_len ref rows Extra
151
1 SIMPLE t2 index NULL idx_t2_1 # NULL # Using index
152
explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
153
id select_type table type possible_keys key key_len ref rows Extra
154
1 SIMPLE t1 range NULL idx_t1_1 518 NULL 5 Using index for group-by
155
explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
156
id select_type table type possible_keys key key_len ref rows Extra
157
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using index for group-by
158
explain select min(a2) from t1 group by a1;
159
id select_type table type possible_keys key key_len ref rows Extra
160
1 SIMPLE t1 range NULL idx_t1_1 518 NULL 5 Using index for group-by
161
explain select a2, min(c), max(c) from t1 group by a1,a2,b;
162
id select_type table type possible_keys key key_len ref rows Extra
163
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using index for group-by
164
select a1, min(a2) from t1 group by a1;
165
a1 min(a2)
166
a a
167
b a
168
c a
169
d a
170
select a1, max(a2) from t1 group by a1;
171
a1 max(a2)
172
a b
173
b b
174
c b
175
d b
176
select a1, min(a2), max(a2) from t1 group by a1;
177
a1 min(a2) max(a2)
178
a a b
179
b a b
180
c a b
181
d a b
182
select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
183
a1 a2 b min(c) max(c)
184
a a a a111 d111
185
a a b e112 h112
186
a b a i121 l121
187
a b b m122 p122
188
b a a a211 d211
189
b a b e212 h212
190
b b a i221 l221
191
b b b m222 p222
192
c a a a311 d311
193
c a b e312 h312
194
c b a i321 l321
195
c b b m322 p322
196
d a a a411 d411
197
d a b e412 h412
198
d b a i421 l421
199
d b b m422 p422
200
select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
201
a1 a2 b max(c) min(c)
202
a a a d111 a111
203
a a b h112 e112
204
a b a l121 i121
205
a b b p122 m122
206
b a a d211 a211
207
b a b h212 e212
208
b b a l221 i221
209
b b b p222 m222
210
c a a d311 a311
211
c a b h312 e312
212
c b a l321 i321
213
c b b p322 m322
214
d a a d411 a411
215
d a b h412 e412
216
d b a l421 i421
217
d b b p422 m422
218
select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
219
a1 a2 b max(c) min(c)
220
a a NULL a999 a777
221
a a a d111 a111
222
a a b h112 e112
223
a b a l121 i121
224
a b b p122 m122
225
b a a d211 a211
226
b a b h212 e212
227
b b a l221 i221
228
b b b p222 m222
229
c a NULL c999 c777
230
c a a d311 a311
231
c a b h312 e312
232
c b a l321 i321
233
c b b p322 m322
234
d a a d411 a411
235
d a b h412 e412
236
d b a l421 i421
237
d b b p422 m422
238
e a a NULL NULL
239
e a b NULL NULL
240
select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
241
min(a2) a1 max(a2) min(a2) a1
242
a a b a a
243
a b b a b
244
a c b a c
245
a d b a d
246
select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
247
a1 b min(c) a1 max(c) b a2 max(c) max(c)
248
a a a111 a d111 a a d111 d111
249
a b e112 a h112 b a h112 h112
250
a a i121 a l121 a b l121 l121
251
a b m122 a p122 b b p122 p122
252
b a a211 b d211 a a d211 d211
253
b b e212 b h212 b a h212 h212
254
b a i221 b l221 a b l221 l221
255
b b m222 b p222 b b p222 p222
256
c a a311 c d311 a a d311 d311
257
c b e312 c h312 b a h312 h312
258
c a i321 c l321 a b l321 l321
259
c b m322 c p322 b b p322 p322
260
d a a411 d d411 a a d411 d411
261
d b e412 d h412 b a h412 h412
262
d a i421 d l421 a b l421 l421
263
d b m422 d p422 b b p422 p422
264
select min(a2) from t1 group by a1;
265
min(a2)
266
a
267
a
268
a
269
a
270
select a2, min(c), max(c) from t1 group by a1,a2,b;
271
a2 min(c) max(c)
272
a a111 d111
273
a e112 h112
274
b i121 l121
275
b m122 p122
276
a a211 d211
277
a e212 h212
278
b i221 l221
279
b m222 p222
280
a a311 d311
281
a e312 h312
282
b i321 l321
283
b m322 p322
284
a a411 d411
285
a e412 h412
286
b i421 l421
287
b m422 p422
288
explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
289
id select_type table type possible_keys key key_len ref rows Extra
290
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
291
explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
292
id select_type table type possible_keys key key_len ref rows Extra
293
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
294
explain select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
295
id select_type table type possible_keys key key_len ref rows Extra
296
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
297
explain select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
298
id select_type table type possible_keys key key_len ref rows Extra
299
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
300
explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
301
id select_type table type possible_keys key key_len ref rows Extra
302
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 17 Using where; Using index for group-by
303
explain select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
304
id select_type table type possible_keys key key_len ref rows Extra
305
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 8 Using where; Using index for group-by
306
explain select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
307
id select_type table type possible_keys key key_len ref rows Extra
308
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 8 Using where; Using index for group-by
309
explain select a1,a2,b, max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
310
id select_type table type possible_keys key key_len ref rows Extra
311
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 8 Using where; Using index for group-by
312
explain select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
313
id select_type table type possible_keys key key_len ref rows Extra
314
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 8 Using where; Using index for group-by
315
explain select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
316
id select_type table type possible_keys key key_len ref rows Extra
317
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
318
explain select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b;
319
id select_type table type possible_keys key key_len ref rows Extra
320
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
321
explain select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b;
322
id select_type table type possible_keys key key_len ref rows Extra
323
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 584 NULL # Using where; Using index for group-by
324
explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
325
id select_type table type possible_keys key key_len ref rows Extra
326
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 651 NULL # Using where; Using index for group-by
327
explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
328
id select_type table type possible_keys key key_len ref rows Extra
329
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 651 NULL # Using where; Using index for group-by
330
explain select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
331
id select_type table type possible_keys key key_len ref rows Extra
332
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 584 NULL # Using where; Using index for group-by
333
explain select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
334
id select_type table type possible_keys key key_len ref rows Extra
335
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 584 NULL # Using where; Using index for group-by
336
explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
337
id select_type table type possible_keys key key_len ref rows Extra
338
1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 651 NULL # Using where; Using index
339
explain select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
340
id select_type table type possible_keys key key_len ref rows Extra
341
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 584 NULL # Using where; Using index for group-by
342
explain select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
343
id select_type table type possible_keys key key_len ref rows Extra
344
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 651 NULL # Using where; Using index for group-by
345
explain select a1,a2,b, max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
346
id select_type table type possible_keys key key_len ref rows Extra
347
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 584 NULL # Using where; Using index for group-by
348
explain select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
349
id select_type table type possible_keys key key_len ref rows Extra
350
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 651 NULL # Using where; Using index for group-by
351
explain select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
352
id select_type table type possible_keys key key_len ref rows Extra
353
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 651 NULL # Using where; Using index for group-by
354
explain select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b;
355
id select_type table type possible_keys key key_len ref rows Extra
356
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 584 NULL # Using where; Using index for group-by
357
select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
358
a1 a2 b min(c) max(c)
359
a a a a111 d111
360
a a b e112 h112
361
a b a i121 l121
362
a b b m122 p122
363
b a a a211 d211
364
b a b e212 h212
365
b b a i221 l221
366
b b b m222 p222
367
c a a a311 d311
368
c a b e312 h312
369
c b a i321 l321
370
c b b m322 p322
371
select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
372
a1 a2 b min(c) max(c)
373
b a a a211 d211
374
b a b e212 h212
375
b b a i221 l221
376
b b b m222 p222
377
c a a a311 d311
378
c a b e312 h312
379
c b a i321 l321
380
c b b m322 p322
381
d a a a411 d411
382
d a b e412 h412
383
d b a i421 l421
384
d b b m422 p422
385
select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
386
a1 a2 b max(c)
387
a a a d111
388
a a b h112
389
a b a l121
390
a b b p122
391
c a a d311
392
c a b h312
393
c b a l321
394
c b b p322
395
d a a d411
396
d a b h412
397
d b a l421
398
d b b p422
399
select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
400
a1 max(c)
401
a d111
402
a h112
403
a l121
404
a p122
405
c d311
406
c h312
407
c l321
408
c p322
409
d d411
410
d h412
411
d l421
412
d p422
413
select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
414
a1 a2 b min(c) max(c)
415
a a a a111 d111
416
a a b e112 h112
417
b a a a211 d211
418
b a b e212 h212
419
c a a a311 d311
420
c a b e312 h312
421
c b a i321 l321
422
c b b m322 p322
423
d a a a411 d411
424
d a b e412 h412
425
d b a i421 l421
426
d b b m422 p422
427
select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
428
a1 a2 b max(c)
429
b a a d211
430
b a b h212
431
b b a l221
432
b b b p222
433
d a a d411
434
d a b h412
435
d b a l421
436
d b b p422
437
select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
438
a1 a2 b min(c) max(c)
439
b a a a211 d211
440
b a b e212 h212
441
b b a i221 l221
442
b b b m222 p222
443
d a a a411 d411
444
d a b e412 h412
445
d b a i421 l421
446
d b b m422 p422
447
select a1,a2,b, max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
448
a1 a2 b max(c)
449
a b a l121
450
a b b p122
451
b b a l221
452
b b b p222
453
c b a l321
454
c b b p322
455
d b a l421
456
d b b p422
457
select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
458
a1 a2 b min(c) max(c)
459
a b a i121 l121
460
a b b m122 p122
461
b b a i221 l221
462
b b b m222 p222
463
c b a i321 l321
464
c b b m322 p322
465
d b a i421 l421
466
d b b m422 p422
467
select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
468
a1 min(c) max(c)
469
b a211 d211
470
b e212 h212
471
b i221 l221
472
b m222 p222
473
c a311 d311
474
c e312 h312
475
c i321 l321
476
c m322 p322
477
d a411 d411
478
d e412 h412
479
d i421 l421
480
d m422 p422
481
select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b;
482
a1 max(c)
483
a d111
484
a h112
485
a l121
486
a p122
487
b d211
488
b h212
489
b l221
490
b p222
491
d d411
492
d h412
493
d l421
494
d p422
495
select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b;
496
a1 a2 b max(c)
497
a a NULL a999
498
a a a d111
499
a a b h112
500
a b a l121
501
a b b p122
502
b a a d211
503
b a b h212
504
b b a l221
505
b b b p222
506
c a NULL c999
507
c a a d311
508
c a b h312
509
c b a l321
510
c b b p322
511
select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
512
a1 a2 b min(c) max(c)
513
a a NULL a777 a999
514
a a a a111 d111
515
a a b e112 h112
516
a b a i121 l121
517
a b b m122 p122
518
b a a a211 d211
519
b a b e212 h212
520
b b a i221 l221
521
b b b m222 p222
522
c a NULL c777 c999
523
c a a a311 d311
524
c a b e312 h312
525
c b a i321 l321
526
c b b m322 p322
527
select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
528
a1 a2 b min(c) max(c)
529
b a a a211 d211
530
b a b e212 h212
531
b b a i221 l221
532
b b b m222 p222
533
c a NULL c777 c999
534
c a a a311 d311
535
c a b e312 h312
536
c b a i321 l321
537
c b b m322 p322
538
d a a a411 d411
539
d a b e412 h412
540
d b a i421 l421
541
d b b m422 p422
542
e a a NULL NULL
543
e a b NULL NULL
544
select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
545
a1 a2 b max(c)
546
a a NULL a999
547
a a a d111
548
a a b h112
549
a b a l121
550
a b b p122
551
c a NULL c999
552
c a a d311
553
c a b h312
554
c b a l321
555
c b b p322
556
d a a d411
557
d a b h412
558
d b a l421
559
d b b p422
560
e a a NULL
561
e a b NULL
562
select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
563
a1 max(c)
564
a a999
565
a d111
566
a h112
567
a l121
568
a p122
569
c c999
570
c d311
571
c h312
572
c l321
573
c p322
574
d d411
575
d h412
576
d l421
577
d p422
578
e NULL
579
e NULL
580
select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
581
a1 a2 b min(c) max(c)
582
a a NULL a777 a999
583
a a a a111 d111
584
a a b e112 h112
585
b a a a211 d211
586
b a b e212 h212
587
c a NULL c777 c999
588
c a a a311 d311
589
c a b e312 h312
590
c b a i321 l321
591
c b b m322 p322
592
d a a a411 d411
593
d a b e412 h412
594
d b a i421 l421
595
d b b m422 p422
596
e a a NULL NULL
597
e a b NULL NULL
598
select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
599
a1 a2 b max(c)
600
b a a d211
601
b a b h212
602
b b a l221
603
b b b p222
604
d a a d411
605
d a b h412
606
d b a l421
607
d b b p422
608
select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
609
a1 a2 b min(c) max(c)
610
b a a a211 d211
611
b a b e212 h212
612
b b a i221 l221
613
b b b m222 p222
614
d a a a411 d411
615
d a b e412 h412
616
d b a i421 l421
617
d b b m422 p422
618
select a1,a2,b, max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
619
a1 a2 b max(c)
620
a b a l121
621
a b b p122
622
b b a l221
623
b b b p222
624
c b a l321
625
c b b p322
626
d b a l421
627
d b b p422
628
select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
629
a1 a2 b min(c) max(c)
630
a b a i121 l121
631
a b b m122 p122
632
b b a i221 l221
633
b b b m222 p222
634
c b a i321 l321
635
c b b m322 p322
636
d b a i421 l421
637
d b b m422 p422
638
select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
639
a1 min(c) max(c)
640
b a211 d211
641
b e212 h212
642
b i221 l221
643
b m222 p222
644
c c777 c999
645
c a311 d311
646
c e312 h312
647
c i321 l321
648
c m322 p322
649
d a411 d411
650
d e412 h412
651
d i421 l421
652
d m422 p422
653
e NULL NULL
654
e NULL NULL
655
select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b;
656
a1 max(c)
657
a a999
658
a d111
659
a h112
660
a l121
661
a p122
662
b d211
663
b h212
664
b l221
665
b p222
666
d d411
667
d h412
668
d l421
669
d p422
670
explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
671
id select_type table type possible_keys key key_len ref rows Extra
672
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 5 Using where; Using index for group-by
673
explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
674
id select_type table type possible_keys key key_len ref rows Extra
675
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 5 Using where; Using index for group-by
676
explain select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2;
677
id select_type table type possible_keys key key_len ref rows Extra
678
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 9 Using where; Using index for group-by
679
explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
680
id select_type table type possible_keys key key_len ref rows Extra
681
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 9 Using where; Using index for group-by
682
explain select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2;
683
id select_type table type possible_keys key key_len ref rows Extra
684
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 9 Using where; Using index for group-by
685
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
686
id select_type table type possible_keys key key_len ref rows Extra
687
1 SIMPLE t2 range NULL idx_t2_1 651 NULL 5 Using where; Using index for group-by
688
explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
689
id select_type table type possible_keys key key_len ref rows Extra
690
1 SIMPLE t2 range NULL idx_t2_1 651 NULL 5 Using where; Using index for group-by
691
explain select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2;
692
id select_type table type possible_keys key key_len ref rows Extra
693
1 SIMPLE t2 range NULL idx_t2_1 584 NULL 10 Using where; Using index for group-by
694
explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
695
id select_type table type possible_keys key key_len ref rows Extra
696
1 SIMPLE t2 range NULL idx_t2_1 651 NULL 10 Using where; Using index for group-by
697
explain select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2;
698
id select_type table type possible_keys key key_len ref rows Extra
699
1 SIMPLE t2 range NULL idx_t2_1 584 NULL 10 Using where; Using index for group-by
700
explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
701
id select_type table type possible_keys key key_len ref rows Extra
702
1 SIMPLE t3 range NULL idx_t3_1 21 NULL 4 Using where; Using index for group-by
703
explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
704
id select_type table type possible_keys key key_len ref rows Extra
705
1 SIMPLE t3 range NULL idx_t3_1 21 NULL 4 Using where; Using index for group-by
706
select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
707
a1 a2 b max(c) min(c)
708
a a b h112 e112
709
b a b h212 e212
710
c a b h312 e312
711
d a b h412 e412
712
select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
713
a1 max(c) min(c)
714
a h112 e112
715
b h212 e212
716
c h312 e312
717
d h412 e412
718
select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2;
719
a1 a2 b max(c)
720
a a b h112
721
a b b p122
722
b a b h212
723
b b b p222
724
c a b h312
725
c b b p322
726
d a b h412
727
d b b p422
728
select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
729
a1 a2 b min(c) max(c)
730
a a b e112 h112
731
a b b m122 p122
732
b a b e212 h212
733
b b b m222 p222
734
c a b e312 h312
735
c b b m322 p322
736
d a b e412 h412
737
d b b m422 p422
738
select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2;
739
a1 a2 max(c)
740
a a h112
741
a b p122
742
b a h212
743
b b p222
744
c a h312
745
c b p322
746
d a h412
747
d b p422
748
select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
749
a1 a2 b max(c) min(c)
750
a a b h112 e112
751
b a b h212 e212
752
c a b h312 e312
753
d a b h412 e412
754
e a b NULL NULL
755
select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
756
a1 max(c) min(c)
757
a h112 e112
758
b h212 e212
759
c h312 e312
760
d h412 e412
761
e NULL NULL
762
select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2;
763
a1 a2 b max(c)
764
a a b h112
765
a b b p122
766
b a b h212
767
b b b p222
768
c a b h312
769
c b b p322
770
d a b h412
771
d b b p422
772
e a b NULL
773
select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
774
a1 a2 b min(c) max(c)
775
a a b e112 h112
776
a b b m122 p122
777
b a b e212 h212
778
b b b m222 p222
779
c a b e312 h312
780
c b b m322 p322
781
d a b e412 h412
782
d b b m422 p422
783
e a b NULL NULL
784
select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2;
785
a1 a2 max(c)
786
a a h112
787
a b p122
788
b a h212
789
b b p222
790
c a h312
791
c b p322
792
d a h412
793
d b p422
794
e a NULL
795
select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
796
a1 a2 b max(c) min(c)
797
a a b h112 e112
798
b a b h212 e212
799
c a b h312 e312
800
select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
801
a1 max(c) min(c)
802
a h112 e112
803
b h212 e212
804
c h312 e312
805
explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
806
id select_type table type possible_keys key key_len ref rows Extra
807
1 SIMPLE t2 range NULL idx_t2_1 651 NULL 5 Using where; Using index for group-by
808
explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
809
id select_type table type possible_keys key key_len ref rows Extra
810
1 SIMPLE t2 range NULL idx_t2_1 584 NULL 5 Using where; Using index for group-by
811
explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
812
id select_type table type possible_keys key key_len ref rows Extra
813
1 SIMPLE t2 range NULL idx_t2_1 651 NULL 10 Using where; Using index for group-by
814
explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
815
id select_type table type possible_keys key key_len ref rows Extra
816
1 SIMPLE t2 range NULL idx_t2_1 584 NULL 10 Using where; Using index for group-by
817
explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
818
id select_type table type possible_keys key key_len ref rows Extra
819
1 SIMPLE t2 range NULL idx_t2_1 651 NULL 10 Using where; Using index for group-by
820
explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
821
id select_type table type possible_keys key key_len ref rows Extra
822
1 SIMPLE t2 range NULL idx_t2_1 651 NULL 10 Using where; Using index for group-by
823
select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
824
a1 a2 b min(c)
825
a a NULL a777
826
c a NULL c777
827
select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
828
a1 a2 b max(c)
829
a a NULL a999
830
c a NULL c999
831
select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
832
a1 a2 b min(c)
833
a a NULL a777
834
c a NULL c777
835
select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
836
a1 a2 b max(c)
837
a a NULL a999
838
c a NULL c999
839
select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
840
a1 a2 b min(c) max(c)
841
a a NULL a777 a999
842
c a NULL c777 c999
843
select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
844
a1 a2 b min(c) max(c)
845
a a NULL a777 a999
846
c a NULL c777 c999
847
explain select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b;
848
id select_type table type possible_keys key key_len ref rows Extra
849
1 SIMPLE t1 range NULL idx_t1_1 585 NULL # Using where; Using index for group-by
850
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
851
id select_type table type possible_keys key key_len ref rows Extra
852
1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by
853
explain select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b;
854
id select_type table type possible_keys key key_len ref rows Extra
855
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using where; Using index for group-by
856
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
857
id select_type table type possible_keys key key_len ref rows Extra
858
1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by
859
explain select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b;
860
id select_type table type possible_keys key key_len ref rows Extra
861
1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by
862
explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
863
id select_type table type possible_keys key key_len ref rows Extra
864
1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by
865
explain select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b;
866
id select_type table type possible_keys key key_len ref rows Extra
867
1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by
868
explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
869
id select_type table type possible_keys key key_len ref rows Extra
870
1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by
871
explain select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
872
id select_type table type possible_keys key key_len ref rows Extra
873
1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by
874
explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
875
id select_type table type possible_keys key key_len ref rows Extra
876
1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by
877
explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
878
id select_type table type possible_keys key key_len ref rows Extra
879
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using where; Using index for group-by
880
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
881
id select_type table type possible_keys key key_len ref rows Extra
882
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using where; Using index for group-by
883
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
884
id select_type table type possible_keys key key_len ref rows Extra
885
1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by
886
explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
887
id select_type table type possible_keys key key_len ref rows Extra
888
1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by
889
explain select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
890
id select_type table type possible_keys key key_len ref rows Extra
891
1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by
892
explain select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b;
893
id select_type table type possible_keys key key_len ref rows Extra
894
1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by
895
explain select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b;
896
id select_type table type possible_keys key key_len ref rows Extra
897
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
898
explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
899
id select_type table type possible_keys key key_len ref rows Extra
900
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
901
explain select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b;
902
id select_type table type possible_keys key key_len ref rows Extra
903
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
904
explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
905
id select_type table type possible_keys key key_len ref rows Extra
906
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
907
explain select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b;
908
id select_type table type possible_keys key key_len ref rows Extra
909
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
910
explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
911
id select_type table type possible_keys key key_len ref rows Extra
912
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
913
explain select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b;
914
id select_type table type possible_keys key key_len ref rows Extra
915
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
916
explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
917
id select_type table type possible_keys key key_len ref rows Extra
918
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
919
explain select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
920
id select_type table type possible_keys key key_len ref rows Extra
921
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
922
explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
923
id select_type table type possible_keys key key_len ref rows Extra
924
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
925
explain select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
926
id select_type table type possible_keys key key_len ref rows Extra
927
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
928
explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
929
id select_type table type possible_keys key key_len ref rows Extra
930
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
931
explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
932
id select_type table type possible_keys key key_len ref rows Extra
933
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
934
explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
935
id select_type table type possible_keys key key_len ref rows Extra
936
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
937
explain select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
938
id select_type table type possible_keys key key_len ref rows Extra
939
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
940
select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b;
941
a1 a2 b max(c)
942
a a a d111
943
a a b h112
944
a b a l121
945
a b b p122
946
b a a d211
947
b a b h212
948
b b a l221
949
b b b p222
950
c a a d311
951
c a b h312
952
c b a l321
953
c b b p322
954
d a a d411
955
d a b h412
956
d b a l421
957
d b b p422
958
select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b;
959
a1 a2 b max(c)
960
a a b h112
961
a b a l121
962
a b b p122
963
b a b h212
964
b b a l221
965
b b b p222
966
c a b h312
967
c b a l321
968
c b b p322
969
d a b h412
970
d b a l421
971
d b b p422
972
select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b;
973
a1 a2 b max(c)
974
select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
975
a1 a2 b min(c) max(c)
976
select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b;
977
a1 a2 b max(c)
978
a a a d111
979
a a b h112
980
a b a k121
981
b a a d211
982
b a b h212
983
b b a k221
984
c a a d311
985
c a b h312
986
c b a j321
987
d a a d411
988
d a b h412
989
d b a j421
990
select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
991
a1 a2 b min(c) max(c)
992
a a a a111 d111
993
a a b e112 h112
994
a b a i121 k121
995
b a a a211 d211
996
b a b e212 h212
997
b b a i221 k221
998
c a a a311 d311
999
c a b e312 h312
1000
c b a i321 j321
1001
d a a a411 d411
1002
d a b e412 h412
1003
d b a i421 j421
1004
select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1005
a1 a2 b max(c)
1006
a a a d111
1007
a a b h112
1008
a b a l121
1009
a b b p122
1010
b a a d211
1011
b a b h212
1012
b b a l221
1013
b b b p222
1014
c a a d311
1015
c a b h312
1016
c b a l321
1017
c b b p322
1018
d a a d411
1019
d a b h412
1020
d b a l421
1021
d b b p422
1022
select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1023
a1 a2 b max(c)
1024
a a a d111
1025
a a b h112
1026
a b a l121
1027
a b b p122
1028
b a a d211
1029
b a b h212
1030
b b a l221
1031
b b b p222
1032
c a a d311
1033
c a b h312
1034
c b a l321
1035
c b b p322
1036
d a a d411
1037
d a b h412
1038
d b a l421
1039
d b b p422
1040
select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1041
a1 a2 b min(c) max(c)
1042
a a a a111 d111
1043
a a b e112 h112
1044
a b a i121 l121
1045
a b b m122 p122
1046
b a a a211 d211
1047
b a b e212 h212
1048
b b a i221 l221
1049
b b b m222 p222
1050
c a a a311 d311
1051
c a b e312 h312
1052
c b a i321 l321
1053
c b b m322 p322
1054
d a a a411 d411
1055
d a b e412 h412
1056
d b a i421 l421
1057
d b b m422 p422
1058
select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
1059
a1 a2 b min(c) max(c)
1060
a a a c111 d111
1061
a a b e112 g112
1062
b a a b211 d211
1063
b a b e212 f212
1064
c a a b311 d311
1065
c a b e312 f312
1066
d a a b411 d411
1067
d a b e412 f412
1068
select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
1069
a1 a2 b min(c) max(c)
1070
a a a a111 c111
1071
b a a a211 c211
1072
c a a a311 c311
1073
d a a a411 c411
1074
d a b g412 g412
1075
d b a k421 k421
1076
select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
1077
a1 a2 b min(c) max(c)
1078
a a a c111 d111
1079
a a b e112 h112
1080
b a a b211 d211
1081
b a b e212 h212
1082
c a a b311 d311
1083
c a b e312 h312
1084
d a a b411 d411
1085
d a b e412 h412
1086
select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b;
1087
a1 a2 b min(c) max(c)
1088
a a a b111 d111
1089
a a b e112 h112
1090
b a a b211 d211
1091
b a b e212 h212
1092
c a a b311 d311
1093
c a b e312 h312
1094
d a a b411 d411
1095
d a b e412 h412
1096
select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b;
1097
a1 a2 b max(c)
1098
a a a d111
1099
a a b h112
1100
a b a l121
1101
a b b p122
1102
b a a d211
1103
b a b h212
1104
b b a l221
1105
b b b p222
1106
c a NULL c999
1107
c a a d311
1108
c a b h312
1109
c b a l321
1110
c b b p322
1111
d a a d411
1112
d a b h412
1113
d b a l421
1114
d b b p422
1115
select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
1116
a1 a2 b min(c) max(c)
1117
a a a b111 d111
1118
a a b e112 h112
1119
a b a i121 l121
1120
a b b m122 p122
1121
b a a b211 d211
1122
b a b e212 h212
1123
b b a i221 l221
1124
b b b m222 p222
1125
c a NULL c777 c999
1126
c a a b311 d311
1127
c a b e312 h312
1128
c b a i321 l321
1129
c b b m322 p322
1130
d a a b411 d411
1131
d a b e412 h412
1132
d b a i421 l421
1133
d b b m422 p422
1134
select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b;
1135
a1 a2 b max(c)
1136
a a b h112
1137
a b a l121
1138
a b b p122
1139
b a b h212
1140
b b a l221
1141
b b b p222
1142
c a b h312
1143
c b a l321
1144
c b b p322
1145
d a b h412
1146
d b a l421
1147
d b b p422
1148
select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
1149
a1 a2 b min(c) max(c)
1150
a a b g112 h112
1151
a b a i121 l121
1152
a b b m122 p122
1153
b a b f212 h212
1154
b b a i221 l221
1155
b b b m222 p222
1156
c a b f312 h312
1157
c b a i321 l321
1158
c b b m322 p322
1159
d a b f412 h412
1160
d b a i421 l421
1161
d b b m422 p422
1162
select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b;
1163
a1 a2 b max(c)
1164
select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
1165
a1 a2 b min(c) max(c)
1166
select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b;
1167
a1 a2 b max(c)
1168
a a NULL a999
1169
a a a d111
1170
a a b h112
1171
a b a k121
1172
b a a d211
1173
b a b h212
1174
b b a k221
1175
c a NULL c999
1176
c a a d311
1177
c a b h312
1178
c b a j321
1179
d a a d411
1180
d a b h412
1181
d b a j421
1182
select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
1183
a1 a2 b min(c) max(c)
1184
a a NULL a777 a999
1185
a a a a111 d111
1186
a a b e112 h112
1187
a b a i121 k121
1188
b a a a211 d211
1189
b a b e212 h212
1190
b b a i221 k221
1191
c a NULL c777 c999
1192
c a a a311 d311
1193
c a b e312 h312
1194
c b a i321 j321
1195
d a a a411 d411
1196
d a b e412 h412
1197
d b a i421 j421
1198
select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1199
a1 a2 b max(c)
1200
a a a d111
1201
a a b h112
1202
a b a l121
1203
a b b p122
1204
b a a d211
1205
b a b h212
1206
b b a l221
1207
b b b p222
1208
c a NULL c999
1209
c a a d311
1210
c a b h312
1211
c b a l321
1212
c b b p322
1213
d a a d411
1214
d a b h412
1215
d b a l421
1216
d b b p422
1217
select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1218
a1 a2 b min(c) max(c)
1219
a a a b111 d111
1220
a a b e112 h112
1221
a b a i121 l121
1222
a b b m122 p122
1223
b a a b211 d211
1224
b a b e212 h212
1225
b b a i221 l221
1226
b b b m222 p222
1227
c a NULL c777 c999
1228
c a a b311 d311
1229
c a b e312 h312
1230
c b a i321 l321
1231
c b b m322 p322
1232
d a a b411 d411
1233
d a b e412 h412
1234
d b a i421 l421
1235
d b b m422 p422
1236
select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1237
a1 a2 b max(c)
1238
a a NULL a999
1239
a a a d111
1240
a a b h112
1241
a b a l121
1242
a b b p122
1243
b a a d211
1244
b a b h212
1245
b b a l221
1246
b b b p222
1247
c a NULL c999
1248
c a a d311
1249
c a b h312
1250
c b a l321
1251
c b b p322
1252
d a a d411
1253
d a b h412
1254
d b a l421
1255
d b b p422
1256
select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1257
a1 a2 b min(c) max(c)
1258
a a NULL a777 a999
1259
a a a a111 d111
1260
a a b e112 h112
1261
a b a i121 l121
1262
a b b m122 p122
1263
b a a a211 d211
1264
b a b e212 h212
1265
b b a i221 l221
1266
b b b m222 p222
1267
c a NULL c777 c999
1268
c a a a311 d311
1269
c a b e312 h312
1270
c b a i321 l321
1271
c b b m322 p322
1272
d a a a411 d411
1273
d a b e412 h412
1274
d b a i421 l421
1275
d b b m422 p422
1276
select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
1277
a1 a2 b min(c) max(c)
1278
a a a c111 d111
1279
a a b e112 g112
1280
b a a b211 d211
1281
b a b e212 f212
1282
c a NULL c777 c999
1283
c a a b311 d311
1284
c a b e312 f312
1285
d a a b411 d411
1286
d a b e412 f412
1287
select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
1288
a1 a2 b min(c) max(c)
1289
a a NULL a777 a999
1290
a a a a111 c111
1291
b a a a211 c211
1292
c a a a311 c311
1293
d a a a411 c411
1294
d a b g412 g412
1295
d b a k421 k421
1296
select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
1297
a1 a2 b min(c) max(c)
1298
a a a c111 d111
1299
a a b e112 h112
1300
b a a b211 d211
1301
b a b e212 h212
1302
c a NULL c777 c999
1303
c a a b311 d311
1304
c a b e312 h312
1305
d a a b411 d411
1306
d a b e412 h412
1307
explain select a1,a2,b,min(c),max(c) from t1
1308
where exists ( select * from t2 where t2.c = t1.c )
1309
group by a1,a2,b;
1310
id select_type table type possible_keys key key_len ref rows Extra
1311
1 PRIMARY t1 index NULL idx_t1_1 651 NULL 128 Using where; Using index
1312
2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 651 NULL 164 Using where; Using index
1313
explain select a1,a2,b,min(c),max(c) from t1
1314
where exists ( select * from t2 where t2.c > 'b1' )
1315
group by a1,a2,b;
1316
id select_type table type possible_keys key key_len ref rows Extra
1317
1 PRIMARY t1 range NULL idx_t1_1 585 NULL 17 Using index for group-by
1318
2 SUBQUERY t2 index NULL idx_t2_1 651 NULL 164 Using where; Using index
1319
explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1320
id select_type table type possible_keys key key_len ref rows Extra
1321
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 17 Using where; Using index for group-by
1322
explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
1323
id select_type table type possible_keys key key_len ref rows Extra
1324
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 651 NULL 17 Using where; Using index for group-by
1325
explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1326
id select_type table type possible_keys key key_len ref rows Extra
1327
1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by
1328
explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
1329
id select_type table type possible_keys key key_len ref rows Extra
1330
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 651 NULL 13 Using where; Using index for group-by
1331
explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
1332
id select_type table type possible_keys key key_len ref rows Extra
1333
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 651 NULL 13 Using where; Using index for group-by
1334
explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1335
id select_type table type possible_keys key key_len ref rows Extra
1336
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
1337
explain select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b;
1338
id select_type table type possible_keys key key_len ref rows Extra
1339
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using where; Using index for group-by
1340
explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1341
id select_type table type possible_keys key key_len ref rows Extra
1342
1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 651 NULL # Using where; Using index
1343
explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
1344
id select_type table type possible_keys key key_len ref rows Extra
1345
1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 651 NULL # Using where; Using index
1346
explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1347
id select_type table type possible_keys key key_len ref rows Extra
1348
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
1349
explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
1350
id select_type table type possible_keys key key_len ref rows Extra
1351
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 651 NULL # Using where; Using index for group-by
1352
explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
1353
id select_type table type possible_keys key key_len ref rows Extra
1354
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 651 NULL # Using where; Using index for group-by
1355
explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1356
id select_type table type possible_keys key key_len ref rows Extra
1357
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 651 NULL # Using where; Using index for group-by
1358
select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1359
a1 a2 b min(c) max(c)
1360
a a b e112 h112
1361
b a b e212 h212
1362
c a b e312 h312
1363
c b b m322 p322
1364
d a b e412 h412
1365
d b b m422 p422
1366
select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
1367
a1 a2 b min(c)
1368
b b a k221
1369
c b a k321
1370
d b a k421
1371
select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
1372
a1 a2 b min(c)
1373
b b a k221
1374
c b a k321
1375
d b a k421
1376
select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1377
a1 a2 b min(c)
1378
select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b;
1379
a1 a2 b min(c)
1380
select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1381
a1 a2 b min(c) max(c)
1382
a a b e112 h112
1383
b a b e212 h212
1384
c a b e312 h312
1385
c b b m322 p322
1386
d a b e412 h412
1387
d b b m422 p422
1388
e a b NULL NULL
1389
select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
1390
a1 a2 b min(c) max(c)
1391
a a a c111 d111
1392
a a b e112 h112
1393
b a a b211 d211
1394
b a b e212 h212
1395
c a NULL c777 c999
1396
c a a b311 d311
1397
c a b e312 h312
1398
c b a i321 l321
1399
c b b m322 p322
1400
d a a b411 d411
1401
d a b e412 h412
1402
d b a i421 l421
1403
d b b m422 p422
1404
select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1405
a1 a2 b min(c) max(c)
1406
a b a i121 l121
1407
b b a i221 l221
1408
c b a i321 l321
1409
d b a i421 l421
1410
select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
1411
a1 a2 b min(c)
1412
b b a k221
1413
c b a k321
1414
d b a k421
1415
select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
1416
a1 a2 b min(c)
1417
b b a k221
1418
c b a k321
1419
d b a k421
1420
select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1421
a1 a2 b min(c)
1422
explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1423
id select_type table type possible_keys key key_len ref rows Extra
1424
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 17 Using where; Using index for group-by
1425
explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1426
id select_type table type possible_keys key key_len ref rows Extra
1427
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using where; Using index for group-by
1428
explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1429
id select_type table type possible_keys key key_len ref rows Extra
1430
1 SIMPLE t1 index NULL idx_t1_1 651 NULL 128 Using where; Using index
1431
explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1432
id select_type table type possible_keys key key_len ref rows Extra
1433
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
1434
explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1435
id select_type table type possible_keys key key_len ref rows Extra
1436
1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_2 584 NULL # Using where; Using index
1437
explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1438
id select_type table type possible_keys key key_len ref rows Extra
1439
1 SIMPLE t2 index NULL idx_t2_2 584 NULL # Using where; Using index
1440
explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1441
id select_type table type possible_keys key key_len ref rows Extra
1442
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
1443
explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1444
id select_type table type possible_keys key key_len ref rows Extra
1445
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 584 NULL # Using where; Using index for group-by
1446
select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1447
a1 a2 b
1448
a a b
1449
b a b
1450
c a b
1451
c b b
1452
d a b
1453
d b b
1454
select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1455
a1 a2 b
1456
a b a
1457
b b a
1458
c b a
1459
d b a
1460
select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1461
a1 a2 b c
1462
a b a i121
1463
select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1464
a1 a2 b
1465
select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1466
a1 a2 b
1467
a a b
1468
b a b
1469
c a b
1470
c b b
1471
d a b
1472
d b b
1473
e a b
1474
select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1475
a1 a2 b
1476
a b a
1477
b b a
1478
c b a
1479
d b a
1480
select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1481
a1 a2 b c
1482
a b a i121
1483
select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1484
a1 a2 b
1485
explain select distinct a1,a2,b from t1;
1486
id select_type table type possible_keys key key_len ref rows Extra
1487
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using index for group-by
1488
explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
1489
id select_type table type possible_keys key key_len ref rows Extra
1490
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using where; Using index for group-by
1491
explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1492
id select_type table type possible_keys key key_len ref rows filtered Extra
1493
1 SIMPLE t1 index NULL idx_t1_1 651 NULL 128 50.78 Using where; Using index
1494
Warnings:
1495
Note 1003 select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a2` >= 'b') and (`test`.`t1`.`b` = 'a') and (`test`.`t1`.`c` = 'i121'))
1496
explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1497
id select_type table type possible_keys key key_len ref rows Extra
1498
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
1499
explain select distinct b from t1 where (a2 >= 'b') and (b = 'a');
1500
id select_type table type possible_keys key key_len ref rows Extra
1501
1 SIMPLE t1 index NULL idx_t1_2 585 NULL 128 Using where; Using index
1502
explain select distinct a1,a2,b from t2;
1503
id select_type table type possible_keys key key_len ref rows Extra
1504
1 SIMPLE t2 index NULL idx_t2_2 584 NULL # Using index
1505
explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
1506
id select_type table type possible_keys key key_len ref rows Extra
1507
1 SIMPLE t2 index NULL idx_t2_2 584 NULL # Using where; Using index
1508
explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1509
id select_type table type possible_keys key key_len ref rows filtered Extra
1510
1 SIMPLE t2 index NULL idx_t2_1 651 NULL 164 50.61 Using where; Using index
1511
Warnings:
1512
Note 1003 select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`a2` >= 'b') and (`test`.`t2`.`b` = 'a') and (`test`.`t2`.`c` = 'i121'))
1513
explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1514
id select_type table type possible_keys key key_len ref rows Extra
1515
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 584 NULL # Using where; Using index for group-by
1516
explain select distinct b from t2 where (a2 >= 'b') and (b = 'a');
1517
id select_type table type possible_keys key key_len ref rows Extra
1518
1 SIMPLE t2 index NULL idx_t2_2 584 NULL 164 Using where; Using index
1519
select distinct a1,a2,b from t1;
1520
a1 a2 b
1521
a a a
1522
a a b
1523
a b a
1524
a b b
1525
b a a
1526
b a b
1527
b b a
1528
b b b
1529
c a a
1530
c a b
1531
c b a
1532
c b b
1533
d a a
1534
d a b
1535
d b a
1536
d b b
1537
select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
1538
a1 a2 b
1539
a b a
1540
b b a
1541
c b a
1542
d b a
1543
select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1544
a1 a2 b c
1545
a b a i121
1546
select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1547
a1 a2 b
1548
select distinct b from t1 where (a2 >= 'b') and (b = 'a');
1549
b
1550
a
1551
select distinct a1,a2,b from t2;
1552
a1 a2 b
1553
a a NULL
1554
a a a
1555
a a b
1556
a b a
1557
a b b
1558
b a a
1559
b a b
1560
b b a
1561
b b b
1562
c a NULL
1563
c a a
1564
c a b
1565
c b a
1566
c b b
1567
d a a
1568
d a b
1569
d b a
1570
d b b
1571
e a a
1572
e a b
1573
select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
1574
a1 a2 b
1575
a b a
1576
b b a
1577
c b a
1578
d b a
1579
select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1580
a1 a2 b c
1581
a b a i121
1582
select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1583
a1 a2 b
1584
select distinct b from t2 where (a2 >= 'b') and (b = 'a');
1585
b
1586
a
1587
select distinct t_00.a1
1588
from t1 t_00
1589
where exists ( select * from t2 where a1 = t_00.a1 );
1590
a1
1591
a
1592
b
1593
c
1594
d
1595
select distinct a1,a1 from t1;
1596
a1 a1
1597
a a
1598
b b
1599
c c
1600
d d
1601
select distinct a2,a1,a2,a1 from t1;
1602
a2 a1 a2 a1
1603
a a a a
1604
b a b a
1605
a b a b
1606
b b b b
1607
a c a c
1608
b c b c
1609
a d a d
1610
b d b d
1611
select distinct t1.a1,t2.a1 from t1,t2;
1612
a1 a1
1613
a a
1614
b a
1615
c a
1616
d a
1617
a b
1618
b b
1619
c b
1620
d b
1621
a c
1622
b c
1623
c c
1624
d c
1625
a d
1626
b d
1627
c d
1628
d d
1629
a e
1630
b e
1631
c e
1632
d e
1633
explain select distinct a1,a2,b from t1;
1634
id select_type table type possible_keys key key_len ref rows Extra
1635
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using index for group-by
1636
explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1637
id select_type table type possible_keys key key_len ref rows Extra
1638
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using where; Using index for group-by
1639
explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1640
id select_type table type possible_keys key key_len ref rows Extra
1641
1 SIMPLE t1 index NULL idx_t1_1 651 NULL 128 Using where; Using index
1642
explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1643
id select_type table type possible_keys key key_len ref rows Extra
1644
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
1645
explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1646
id select_type table type possible_keys key key_len ref rows Extra
1647
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using where; Using index for group-by; Using temporary; Using filesort
1648
explain select distinct a1,a2,b from t2;
1649
id select_type table type possible_keys key key_len ref rows Extra
1650
1 SIMPLE t2 index NULL idx_t2_2 584 NULL # Using index
1651
explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1652
id select_type table type possible_keys key key_len ref rows Extra
1653
1 SIMPLE t2 index NULL idx_t2_2 584 NULL # Using where; Using index
1654
explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1655
id select_type table type possible_keys key key_len ref rows Extra
1656
1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index
1657
explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1658
id select_type table type possible_keys key key_len ref rows Extra
1659
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 584 NULL # Using where; Using index for group-by
1660
explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1661
id select_type table type possible_keys key key_len ref rows Extra
1662
1 SIMPLE t2 index NULL idx_t2_2 584 NULL # Using where; Using index; Using temporary; Using filesort
1663
select distinct a1,a2,b from t1;
1664
a1 a2 b
1665
a a a
1666
a a b
1667
a b a
1668
a b b
1669
b a a
1670
b a b
1671
b b a
1672
b b b
1673
c a a
1674
c a b
1675
c b a
1676
c b b
1677
d a a
1678
d a b
1679
d b a
1680
d b b
1681
select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1682
a1 a2 b
1683
a b a
1684
b b a
1685
c b a
1686
d b a
1687
select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1688
a1 a2 b c
1689
a b a i121
1690
select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1691
a1 a2 b
1692
select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1693
b
1694
a
1695
select distinct a1,a2,b from t2;
1696
a1 a2 b
1697
a a NULL
1698
a a a
1699
a a b
1700
a b a
1701
a b b
1702
b a a
1703
b a b
1704
b b a
1705
b b b
1706
c a NULL
1707
c a a
1708
c a b
1709
c b a
1710
c b b
1711
d a a
1712
d a b
1713
d b a
1714
d b b
1715
e a a
1716
e a b
1717
select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1718
a1 a2 b
1719
a b a
1720
b b a
1721
c b a
1722
d b a
1723
select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1724
a1 a2 b c
1725
a b a i121
1726
select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1727
a1 a2 b
1728
select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1729
b
1730
a
1731
explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
1732
id select_type table type possible_keys key key_len ref rows Extra
1733
1 SIMPLE t1 index NULL idx_t1_2 585 NULL 128 Using where; Using index
1734
explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1735
id select_type table type possible_keys key key_len ref rows Extra
1736
1 SIMPLE t1 index NULL idx_t1_1 651 NULL 128 Using where; Using index
1737
explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1738
id select_type table type possible_keys key key_len ref rows filtered Extra
1739
1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 585 NULL 128 75.00 Using where; Using index
1740
Warnings:
1741
Note 1003 select count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `count(distinct a1,a2,b)` from `test`.`t1` where ((`test`.`t1`.`a1` > 'a') and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`b` = 'c'))
1742
explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
1743
id select_type table type possible_keys key key_len ref rows Extra
1744
1 SIMPLE t1 index NULL idx_t1_2 585 NULL 128 Using where; Using index
1745
explain extended select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
1746
id select_type table type possible_keys key key_len ref rows filtered Extra
1747
1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 585 NULL 128 75.00 Using where; Using index
1748
Warnings:
1749
Note 1003 select (ord(`test`.`t1`.`a1`) + count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`)) AS `ord(a1) + count(distinct a1,a2,b)` from `test`.`t1` where ((`test`.`t1`.`a1` > 'a') and (`test`.`t1`.`a2` > 'a'))
1750
select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
1751
count(distinct a1,a2,b)
1752
4
1753
select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1754
count(distinct a1,a2,b,c)
1755
1
1756
select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1757
count(distinct a1,a2,b)
1758
0
1759
select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
1760
count(distinct b)
1761
1
1762
select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
1763
ord(a1) + count(distinct a1,a2,b)
1764
104
1765
explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
1766
id select_type table type possible_keys key key_len ref rows Extra
1767
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
1768
explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
1769
id select_type table type possible_keys key key_len ref rows Extra
1770
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
1771
explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
1772
id select_type table type possible_keys key key_len ref rows Extra
1773
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
1774
explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
1775
id select_type table type possible_keys key key_len ref rows Extra
1776
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 585 NULL 13 Using where; Using index for group-by
1777
explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
1778
id select_type table type possible_keys key key_len ref rows Extra
1779
1 SIMPLE t1 range NULL idx_t1_1 585 NULL 9 Using index for group-by
1780
select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
1781
a1 a2 b concat(min(c), max(c))
1782
a a a a111d111
1783
a a b e112h112
1784
a b a i121l121
1785
a b b m122p122
1786
b a a a211d211
1787
b a b e212h212
1788
b b a i221l221
1789
b b b m222p222
1790
c a a a311d311
1791
c a b e312h312
1792
c b a i321l321
1793
c b b m322p322
1794
select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
1795
concat(a1,min(c)) b
1796
aa111 a
1797
ae112 b
1798
ai121 a
1799
am122 b
1800
ba211 a
1801
be212 b
1802
bi221 a
1803
bm222 b
1804
ca311 a
1805
ce312 b
1806
ci321 a
1807
cm322 b
1808
select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
1809
concat(a1,min(c)) b max(c)
1810
aa111 a d111
1811
ae112 b h112
1812
ai121 a l121
1813
am122 b p122
1814
ba211 a d211
1815
be212 b h212
1816
bi221 a l221
1817
bm222 b p222
1818
ca311 a d311
1819
ce312 b h312
1820
ci321 a l321
1821
cm322 b p322
1822
select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
1823
concat(a1,a2) b min(c) max(c)
1824
aa a a111 d111
1825
aa b e112 h112
1826
ab a i121 l121
1827
ab b m122 p122
1828
ba a a211 d211
1829
ba b e212 h212
1830
bb a i221 l221
1831
bb b m222 p222
1832
ca a a311 d311
1833
ca b e312 h312
1834
cb a i321 l321
1835
cb b m322 p322
1836
select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
1837
concat(ord(min(b)),ord(max(b))) min(b) max(b)
1838
9798 a b
1839
9798 a b
1840
9798 a b
1841
9798 a b
1842
9798 a b
1843
9798 a b
1844
9798 a b
1845
9798 a b
1846
explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b;
1847
id select_type table type possible_keys key key_len ref rows Extra
1848
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort
1849
explain select a1,a2,b,d from t1 group by a1,a2,b;
1850
id select_type table type possible_keys key key_len ref rows Extra
1851
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort
1852
explain extended select a1,a2,min(b),max(b) from t1
1853
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
1854
id select_type table type possible_keys key key_len ref rows filtered Extra
1855
1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 651 NULL 128 49.22 Using where; Using index
1856
Warnings:
1857
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,min(`test`.`t1`.`b`) AS `min(b)`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`c` > 'a111')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`
1858
explain extended select a1,a2,b,min(c),max(c) from t1
1859
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
1860
id select_type table type possible_keys key key_len ref rows filtered Extra
1861
1 SIMPLE t1 ALL idx_t1_0,idx_t1_1,idx_t1_2 NULL NULL NULL 128 49.22 Using where; Using temporary; Using filesort
1862
Warnings:
1863
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,min(`test`.`t1`.`c`) AS `min(c)`,max(`test`.`t1`.`c`) AS `max(c)` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`d` > 'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
1864
explain extended select a1,a2,b,c from t1
1865
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
1866
id select_type table type possible_keys key key_len ref rows filtered Extra
1867
1 SIMPLE t1 ALL idx_t1_0,idx_t1_1,idx_t1_2 NULL NULL NULL 128 49.22 Using where; Using temporary; Using filesort
1868
Warnings:
1869
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`d` > 'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`,`test`.`t1`.`c`
1870
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
1871
id select_type table type possible_keys key key_len ref rows Extra
1872
1 SIMPLE t2 index NULL idx_t2_1 651 NULL 164 Using where; Using index
1873
explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b;
1874
id select_type table type possible_keys key key_len ref rows filtered Extra
1875
1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 651 NULL 128 49.22 Using where; Using index
1876
Warnings:
1877
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`c` > 'a111')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
1878
explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
1879
id select_type table type possible_keys key key_len ref rows Extra
1880
1 SIMPLE t2 index NULL idx_t2_1 651 NULL 164 Using where; Using index
1881
select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
1882
a1 a2 min(b) c
1883
a a a a111
1884
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1;
1885
id select_type table type possible_keys key key_len ref rows Extra
1886
1 SIMPLE t2 index NULL idx_t2_1 651 NULL 164 Using where; Using index
1887
explain select a1,a2,b,min(c),max(c) from t2
1888
where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b;
1889
id select_type table type possible_keys key key_len ref rows Extra
1890
1 SIMPLE t2 index NULL idx_t2_1 651 NULL 164 Using where; Using index
1891
explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;
1892
id select_type table type possible_keys key key_len ref rows Extra
1893
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort
1894
explain select a1,a2,count(a2) from t1 group by a1,a2,b;
1895
id select_type table type possible_keys key key_len ref rows Extra
1896
1 SIMPLE t1 index NULL idx_t1_2 585 NULL 128 Using index
1897
explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
1898
id select_type table type possible_keys key key_len ref rows filtered Extra
1899
1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 651 NULL 128 75.00 Using where; Using index
1900
Warnings:
1901
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
1902
explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
1903
id select_type table type possible_keys key key_len ref rows filtered Extra
1904
1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 651 NULL 128 75.00 Using where; Using index
1905
Warnings:
1906
Note 1003 select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
1907
explain select distinct(a1) from t1 where ord(a2) = 98;
1908
id select_type table type possible_keys key key_len ref rows Extra
1909
1 SIMPLE t1 index NULL idx_t1_2 585 NULL 128 Using where; Using index
1910
select distinct(a1) from t1 where ord(a2) = 98;
1911
a1
1912
a
1913
b
1914
c
1915
d
1916
explain select a1 from t1 where a2 = 'b' group by a1;
1917
id select_type table type possible_keys key key_len ref rows Extra
1918
1 SIMPLE t1 range NULL idx_t1_1 518 NULL 5 Using where; Using index for group-by
1919
select a1 from t1 where a2 = 'b' group by a1;
1920
a1
1921
a
1922
b
1923
c
1924
d
1925
explain select distinct a1 from t1 where a2 = 'b';
1926
id select_type table type possible_keys key key_len ref rows Extra
1927
1 SIMPLE t1 range NULL idx_t1_1 518 NULL 5 Using where; Using index for group-by
1928
select distinct a1 from t1 where a2 = 'b';
1929
a1
1930
a
1931
b
1932
c
1933
d
1934
drop table t1,t2,t3;
1935
create TEMPORARY table t1 (c1 int not null,c2 int not null, primary key(c1,c2)) ENGINE=MYISAM;
1936
insert into t1 (c1,c2) values
1937
(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);
1938
select distinct c1, c2 from t1 order by c2;
1939
c1 c2
1940
10 1
1941
10 2
1942
10 3
1943
20 4
1944
20 5
1945
20 6
1946
30 7
1947
30 8
1948
30 9
1949
select c1,min(c2) as c2 from t1 group by c1 order by c2;
1950
c1 c2
1951
10 1
1952
20 4
1953
30 7
1954
select c1,c2 from t1 group by c1,c2 order by c2;
1955
c1 c2
1956
10 1
1957
10 2
1958
10 3
1959
20 4
1960
20 5
1961
20 6
1962
30 7
1963
30 8
1964
30 9
1965
drop table t1;
1966
CREATE TABLE t1 (a varchar(5), b int, PRIMARY KEY (a,b));
1967
INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
1968
OPTIMIZE TABLE t1;
1969
Table Op Msg_type Msg_text
1970
test.t1 optimize status OK
1971
SELECT a FROM t1 WHERE a='AA' GROUP BY a;
1972
a
1973
AA
1974
SELECT a FROM t1 WHERE a='BB' GROUP BY a;
1975
a
1976
BB
1977
EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;
1978
id select_type table type possible_keys key key_len ref rows Extra
1979
1 SIMPLE t1 ref PRIMARY PRIMARY 22 const 1 Using where; Using index
1980
EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;
1981
id select_type table type possible_keys key key_len ref rows Extra
1982
1 SIMPLE t1 ref PRIMARY PRIMARY 22 const 1 Using where; Using index
1983
SELECT DISTINCT a FROM t1 WHERE a='BB';
1984
a
1985
BB
1986
SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
1987
a
1988
BB
1989
SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;
1990
a
1991
BB
1992
DROP TABLE t1;
1993
CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a));
1994
INSERT INTO t1 (a) VALUES
1995
(''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
1996
('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'),
1997
('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');
1998
EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;
1999
id select_type table type possible_keys key key_len ref rows Extra
2000
1 SIMPLE t1 range NULL PRIMARY 258 NULL 12 Using index for group-by
2001
SELECT DISTINCT a,a FROM t1 ORDER BY a;
2002
a a
2003
2004
CENTRAL CENTRAL
2005
EASTERN EASTERN
2006
GREATER LONDON GREATER LONDON
2007
NORTH CENTRAL NORTH CENTRAL
2008
NORTH EAST NORTH EAST
2009
NORTH WEST NORTH WEST
2010
SCOTLAND SCOTLAND
2011
SOUTH EAST SOUTH EAST
2012
SOUTH WEST SOUTH WEST
2013
WESTERN WESTERN
2014
DROP TABLE t1;
2015
CREATE TABLE t1 (id1 INT, id2 INT);
2016
CREATE TABLE t2 (id2 INT, id3 INT, id5 INT);
2017
CREATE TABLE t3 (id3 INT, id4 INT);
2018
CREATE TABLE t4 (id4 INT);
2019
CREATE TABLE t5 (id5 INT, id6 INT);
2020
CREATE TABLE t6 (id6 INT);
2021
INSERT INTO t1 VALUES(1,1);
2022
INSERT INTO t2 VALUES(1,1,1);
2023
INSERT INTO t3 VALUES(1,1);
2024
INSERT INTO t4 VALUES(1);
2025
INSERT INTO t5 VALUES(1,1);
2026
INSERT INTO t6 VALUES(1);
2027
SELECT * FROM
2028
t1
2029
NATURAL JOIN
2030
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
2031
ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
2032
id2 id1 id3 id5 id4 id3 id6 id5
2033
1 1 1 1 1 1 1 1
2034
SELECT * FROM
2035
t1
2036
NATURAL JOIN
2037
(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2
2038
ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
2039
id2 id1 id4 id3 id6 id5 id3 id5
2040
1 1 1 1 1 1 1 1
2041
SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2);
2042
id2 id1 id3 id4 id6 id5 id3 id5
2043
1 1 1 1 1 1 1 1
2044
SELECT * FROM
2045
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
2046
ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5))
2047
NATURAL JOIN
2048
t1;
2049
id2 id3 id5 id4 id3 id6 id5 id1
2050
1 1 1 1 1 1 1 1
2051
SELECT * FROM
2052
(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6)))
2053
NATURAL JOIN
2054
t1;
2055
id2 id3 id5 id4 id3 id6 id5 id1
2056
1 1 1 1 1 1 1 1
2057
DROP TABLE t1,t2,t3,t4,t5,t6;
2058
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
2059
INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
2060
explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
2061
id select_type table type possible_keys key key_len ref rows Extra
2062
1 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 1 Using where; Using index for group-by
2063
SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
2064
MAX(b) a
2065
1 1
2066
SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
2067
MIN(b) a
2068
2 1
2069
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
2070
INSERT INTO t2 SELECT a,b,b FROM t1;
2071
explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
2072
id select_type table type possible_keys key key_len ref rows Extra
2073
1 SIMPLE t2 range PRIMARY PRIMARY 12 NULL 1 Using where; Using index for group-by
2074
SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
2075
MIN(c)
2076
2
2077
DROP TABLE t1,t2;
2078
CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
2079
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
2080
(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
2081
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
2082
id select_type table type possible_keys key key_len ref rows Extra
2083
1 SIMPLE t1 index NULL a 10 NULL 15 Using index
2084
FLUSH STATUS;
2085
SELECT max(b), a FROM t1 GROUP BY a;
2086
max(b) a
2087
5 1
2088
3 2
2089
1 3
2090
6 4
2091
SHOW STATUS LIKE 'handler_read__e%';
2092
Variable_name Value
2093
Handler_read_key 0
2094
Handler_read_next 0
2095
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
2096
id select_type table type possible_keys key key_len ref rows Extra
2097
1 SIMPLE t1 index NULL a 10 NULL 15 Using index
2098
FLUSH STATUS;
2099
CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
2100
SHOW STATUS LIKE 'handler_read__e%';
2101
Variable_name Value
2102
Handler_read_key 0
2103
Handler_read_next 0
2104
FLUSH STATUS;
2105
SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
2106
max(b) a
2107
5 1
2108
3 2
2109
1 3
2110
6 4
2111
SHOW STATUS LIKE 'handler_read__e%';
2112
Variable_name Value
2113
Handler_read_key 0
2114
Handler_read_next 0
2115
FLUSH STATUS;
2116
(SELECT max(b), a FROM t1 GROUP BY a) UNION
2117
(SELECT max(b), a FROM t1 GROUP BY a);
2118
max(b) a
2119
5 1
2120
3 2
2121
1 3
2122
6 4
2123
SHOW STATUS LIKE 'handler_read__e%';
2124
Variable_name Value
2125
Handler_read_key 0
2126
Handler_read_next 0
2127
EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION
2128
(SELECT max(b), a FROM t1 GROUP BY a);
2129
id select_type table type possible_keys key key_len ref rows Extra
2130
1 PRIMARY t1 index NULL a 10 NULL 15 Using index
2131
2 UNION t1 index NULL a 10 NULL 15 Using index
2132
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
2133
EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
2134
FROM t1 AS t1_outer;
2135
id select_type table type possible_keys key key_len ref rows Extra
2136
1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index
2137
2 SUBQUERY t1 index NULL a 10 NULL 15 Using index
2138
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS
2139
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2140
id select_type table type possible_keys key key_len ref rows Extra
2141
1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index
2142
2 SUBQUERY t1 index NULL a 10 NULL 15 Using index
2143
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
2144
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
2145
id select_type table type possible_keys key key_len ref rows Extra
2146
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2147
2 SUBQUERY t1 index NULL a 10 NULL 15 Using index
2148
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
2149
a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2150
id select_type table type possible_keys key key_len ref rows Extra
2151
1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index
2152
2 SUBQUERY t1 index NULL a 10 NULL 15 Using index
2153
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
2154
a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2155
id select_type table type possible_keys key key_len ref rows Extra
2156
1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index
2157
2 SUBQUERY t1 index NULL a 10 NULL 15 Using index
2158
EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2
2159
ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2)
2160
AND t1_outer1.b = t1_outer2.b;
2161
id select_type table type possible_keys key key_len ref rows Extra
2162
1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index
2163
1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer
2164
2 SUBQUERY t1 index NULL a 10 NULL 15 Using index
2165
EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
2166
FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
2167
id select_type table type possible_keys key key_len ref rows Extra
2168
1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using index
2169
2 SUBQUERY t1_outer index NULL a 10 NULL 15 Using index
2170
3 SUBQUERY t1 index NULL a 10 NULL 15 Using index
2171
CREATE TABLE t3 LIKE t1;
2172
FLUSH STATUS;
2173
INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
2174
SHOW STATUS LIKE 'handler_read__e%';
2175
Variable_name Value
2176
Handler_read_key 0
2177
Handler_read_next 0
2178
DELETE FROM t3;
2179
FLUSH STATUS;
2180
INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2)
2181
FROM t1 LIMIT 1;
2182
SHOW STATUS LIKE 'handler_read__e%';
2183
Variable_name Value
2184
Handler_read_key 0
2185
Handler_read_next 0
2186
FLUSH STATUS;
2187
DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
2188
SHOW STATUS LIKE 'handler_read__e%';
2189
Variable_name Value
2190
Handler_read_key 0
2191
Handler_read_next 0
2192
FLUSH STATUS;
2193
DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x
2194
FROM t1) > 10000;
2195
ERROR 21000: Subquery returns more than 1 row
2196
SHOW STATUS LIKE 'handler_read__e%';
2197
Variable_name Value
2198
Handler_read_key 0
2199
Handler_read_next 0
2200
DROP TABLE t1,t2,t3;
2201
CREATE TABLE t1 (a int, INDEX idx(a));
2202
INSERT INTO t1 VALUES
2203
(4), (2), (1), (2), (4), (2), (1), (4),
2204
(4), (2), (1), (2), (2), (4), (1), (4);
2205
EXPLAIN SELECT DISTINCT(a) FROM t1;
2206
id select_type table type possible_keys key key_len ref rows Extra
2207
1 SIMPLE t1 index NULL idx 5 NULL 16 Using index
2208
SELECT DISTINCT(a) FROM t1;
2209
a
2210
1
2211
2
2212
4
2213
EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
2214
id select_type table type possible_keys key key_len ref rows Extra
2215
1 SIMPLE t1 index NULL idx 5 NULL 16 Using index
2216
SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
2217
a
2218
1
2219
2
2220
4
2221
DROP TABLE t1;
2222
CREATE TABLE t1 (a INT, b INT);
2223
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
2224
INSERT INTO t1 SELECT a + 1, b FROM t1;
2225
INSERT INTO t1 SELECT a + 2, b FROM t1;
2226
EXPLAIN
2227
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2228
id select_type table type possible_keys key key_len ref rows Extra
2229
1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort
2230
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2231
a MIN(b) MAX(b)
2232
4 1 3
2233
3 1 3
2234
2 1 3
2235
1 1 3
2236
CREATE INDEX break_it ON t1 (a, b);
2237
EXPLAIN
2238
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
2239
id select_type table type possible_keys key key_len ref rows Extra
2240
1 SIMPLE t1 index NULL break_it 10 NULL 12 Using index
2241
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
2242
a MIN(b) MAX(b)
2243
1 1 3
2244
2 1 3
2245
3 1 3
2246
4 1 3
2247
EXPLAIN
2248
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2249
id select_type table type possible_keys key key_len ref rows Extra
2250
1 SIMPLE t1 index NULL break_it 10 NULL 12 Using index
2251
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2252
a MIN(b) MAX(b)
2253
4 1 3
2254
3 1 3
2255
2 1 3
2256
1 1 3
2257
EXPLAIN
2258
SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
2259
id select_type table type possible_keys key key_len ref rows Extra
2260
1 SIMPLE t1 index NULL break_it 10 NULL 12 Using index
2261
SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
2262
a MIN(b) MAX(b) AVG(b)
2263
4 1 3 2.0000
2264
3 1 3 2.0000
2265
2 1 3 2.0000
2266
1 1 3 2.0000
2267
DROP TABLE t1;
2268
CREATE TABLE t1 (a int, b int, c int, d int,
2269
KEY foo (c,d,a,b), KEY bar (c,a,b,d));
2270
INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4);
2271
INSERT INTO t1 SELECT * FROM t1;
2272
INSERT INTO t1 SELECT * FROM t1;
2273
INSERT INTO t1 SELECT a,b,c+1,d FROM t1;
2274
EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4;
2275
id select_type table type possible_keys key key_len ref rows Extra
2276
1 SIMPLE t1 index NULL foo 20 NULL 32 Using where; Using index
2277
SELECT DISTINCT c FROM t1 WHERE d=4;
2278
c
2279
1
2280
2
2281
DROP TABLE t1;
Loggerhead is a web-based interface for Breezy
Version: 2.0.1