1
drop table if exists t1;
3
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
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);
42
Table Op Msg_type Msg_text
43
test.t1 analyze status Table is already up to date
44
drop table if exists t2;
46
a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' '
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);
70
Table Op Msg_type Msg_text
71
test.t2 analyze status Table is already up to date
72
drop table if exists t3;
74
a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' '
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);
132
Table Op Msg_type Msg_text
133
test.t3 analyze status Table is already up to date
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 130 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 65 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 130 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 147 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 147 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 range NULL idx_t2_1 # NULL # Using index for group-by
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 130 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 147 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 130 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 147 NULL 17 Using index for group-by
164
select a1, min(a2) from t1 group by a1;
170
select a1, max(a2) from t1 group by a1;
176
select a1, min(a2), max(a2) from t1 group by a1;
182
select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
183
a1 a2 b min(c) max(c)
200
select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
201
a1 a2 b max(c) min(c)
218
select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
219
a1 a2 b max(c) min(c)
240
select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
241
min(a2) a1 max(a2) min(a2) a1
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;
270
select a2, min(c), max(c) from t1 group by a1,a2,b;
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 147 NULL 10 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 147 NULL 14 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 147 NULL 14 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 147 NULL 14 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 147 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 147 NULL 10 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 147 NULL 10 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 147 NULL 10 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 147 NULL 10 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 147 NULL 14 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 147 NULL 14 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 146 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 163 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 163 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 146 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 146 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 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
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 146 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 163 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 146 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 163 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 163 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 146 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)
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)
385
select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
399
select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
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)
427
select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
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)
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;
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)
467
select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
481
select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b;
495
select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b;
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)
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)
544
select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
562
select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
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)
598
select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
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)
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;
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)
638
select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
655
select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b;
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 147 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 147 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 147 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 147 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 147 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 163 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 163 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 146 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 163 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 146 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 6 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 6 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)
712
select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
718
select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2;
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)
738
select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2;
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)
755
select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
762
select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2;
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)
784
select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2;
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)
800
select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
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 163 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 146 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 163 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 146 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 163 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 163 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;
827
select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
831
select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
835
select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
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)
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)
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 147 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 163 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 147 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 163 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 163 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 163 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 163 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 163 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 163 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 163 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 147 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 147 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 163 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 163 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 163 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 163 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 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
940
select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b;
958
select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
959
a1 a2 b min(c) max(c)
976
select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b;
990
select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
991
a1 a2 b min(c) max(c)
1004
select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b;
1006
select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
1007
a1 a2 b min(c) max(c)
1008
select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b;
1022
select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
1023
a1 a2 b min(c) max(c)
1036
select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1054
select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1055
a1 a2 b min(c) max(c)
1072
select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1090
select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1091
a1 a2 b min(c) max(c)
1108
select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
1109
a1 a2 b min(c) max(c)
1118
select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
1119
a1 a2 b min(c) max(c)
1126
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;
1127
a1 a2 b min(c) max(c)
1136
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;
1137
a1 a2 b min(c) max(c)
1146
select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b;
1165
select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
1166
a1 a2 b min(c) max(c)
1184
select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b;
1198
select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
1199
a1 a2 b min(c) max(c)
1212
select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b;
1214
select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
1215
a1 a2 b min(c) max(c)
1216
select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b;
1232
select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
1233
a1 a2 b min(c) max(c)
1248
select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1267
select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1268
a1 a2 b min(c) max(c)
1286
select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1306
select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1307
a1 a2 b min(c) max(c)
1326
select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
1327
a1 a2 b min(c) max(c)
1337
select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
1338
a1 a2 b min(c) max(c)
1346
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;
1347
a1 a2 b min(c) max(c)
1357
explain select a1,a2,b,min(c),max(c) from t1
1358
where exists ( select * from t2 where t2.c = t1.c )
1360
id select_type table type possible_keys key key_len ref rows Extra
1361
1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index
1362
2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
1363
explain select a1,a2,b,min(c),max(c) from t1
1364
where exists ( select * from t2 where t2.c > 'b1' )
1366
id select_type table type possible_keys key key_len ref rows Extra
1367
1 PRIMARY t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by
1368
2 SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
1369
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;
1370
id select_type table type possible_keys key key_len ref rows Extra
1371
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by
1372
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;
1373
id select_type table type possible_keys key key_len ref rows Extra
1374
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 17 Using where; Using index for group-by
1375
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;
1376
id select_type table type possible_keys key key_len ref rows Extra
1377
1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
1378
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;
1379
id select_type table type possible_keys key key_len ref rows Extra
1380
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 14 Using where; Using index for group-by
1381
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;
1382
id select_type table type possible_keys key key_len ref rows Extra
1383
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 14 Using where; Using index for group-by
1384
explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1385
id select_type table type possible_keys key key_len ref rows Extra
1386
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
1387
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;
1388
id select_type table type possible_keys key key_len ref rows Extra
1389
1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by
1390
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;
1391
id select_type table type possible_keys key key_len ref rows Extra
1392
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
1393
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;
1394
id select_type table type possible_keys key key_len ref rows Extra
1395
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
1396
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;
1397
id select_type table type possible_keys key key_len ref rows Extra
1398
1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
1399
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;
1400
id select_type table type possible_keys key key_len ref rows Extra
1401
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
1402
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;
1403
id select_type table type possible_keys key key_len ref rows Extra
1404
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
1405
explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1406
id select_type table type possible_keys key key_len ref rows Extra
1407
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
1408
select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1409
a1 a2 b min(c) max(c)
1416
select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
1417
a1 a2 b min(c) max(c)
1430
select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1431
a1 a2 b min(c) max(c)
1436
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;
1441
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;
1446
select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1448
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;
1450
select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1451
a1 a2 b min(c) max(c)
1459
select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
1460
a1 a2 b min(c) max(c)
1474
select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1475
a1 a2 b min(c) max(c)
1480
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;
1485
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;
1490
select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1492
explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1493
id select_type table type possible_keys key key_len ref rows Extra
1494
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by
1495
explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1496
id select_type table type possible_keys key key_len ref rows Extra
1497
1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by
1498
explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1499
id select_type table type possible_keys key key_len ref rows Extra
1500
1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
1501
explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1502
id select_type table type possible_keys key key_len ref rows Extra
1503
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
1504
explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1505
id select_type table type possible_keys key key_len ref rows Extra
1506
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
1507
explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1508
id select_type table type possible_keys key key_len ref rows Extra
1509
1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by
1510
explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1511
id select_type table type possible_keys key key_len ref rows Extra
1512
1 SIMPLE t2 index NULL idx_t2_1 163 NULL # Using where; Using index
1513
explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
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 146 NULL # Using where; Using index for group-by
1516
select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1524
select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1530
select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1533
select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1535
select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1544
select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1550
select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1553
select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1555
explain select distinct a1,a2,b from t1;
1556
id select_type table type possible_keys key key_len ref rows Extra
1557
1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by
1558
explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
1559
id select_type table type possible_keys key key_len ref rows Extra
1560
1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by
1561
explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1562
id select_type table type possible_keys key key_len ref rows filtered Extra
1563
1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 50.78 Using where; Using index
1565
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"."c" = 'i121') and ("test"."t1"."b" = 'a') and ("test"."t1"."a2" >= 'b'))
1566
explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1567
id select_type table type possible_keys key key_len ref rows Extra
1568
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
1569
explain select distinct b from t1 where (a2 >= 'b') and (b = 'a');
1570
id select_type table type possible_keys key key_len ref rows Extra
1571
1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index
1572
explain select distinct a1,a2,b from t2;
1573
id select_type table type possible_keys key key_len ref rows Extra
1574
1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using index for group-by
1575
explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
1576
id select_type table type possible_keys key key_len ref rows Extra
1577
1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by
1578
explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1579
id select_type table type possible_keys key key_len ref rows filtered Extra
1580
1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 50.61 Using where; Using index
1582
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"."c" = 'i121') and ("test"."t2"."b" = 'a') and ("test"."t2"."a2" >= 'b'))
1583
explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1584
id select_type table type possible_keys key key_len ref rows Extra
1585
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
1586
explain select distinct b from t2 where (a2 >= 'b') and (b = 'a');
1587
id select_type table type possible_keys key key_len ref rows Extra
1588
1 SIMPLE t2 index NULL idx_t2_2 146 NULL 164 Using where; Using index
1589
select distinct a1,a2,b from t1;
1607
select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
1613
select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1616
select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1618
select distinct b from t1 where (a2 >= 'b') and (b = 'a');
1621
select distinct a1,a2,b from t2;
1643
select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
1649
select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1652
select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1654
select distinct b from t2 where (a2 >= 'b') and (b = 'a');
1657
select distinct t_00.a1
1659
where exists ( select * from t2 where a1 = t_00.a1 );
1665
select distinct a1,a1 from t1;
1671
select distinct a2,a1,a2,a1 from t1;
1681
select distinct t1.a1,t2.a1 from t1,t2;
1703
explain select distinct a1,a2,b from t1;
1704
id select_type table type possible_keys key key_len ref rows Extra
1705
1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by
1706
explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1707
id select_type table type possible_keys key key_len ref rows Extra
1708
1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by
1709
explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1710
id select_type table type possible_keys key key_len ref rows Extra
1711
1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
1712
explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1713
id select_type table type possible_keys key key_len ref rows Extra
1714
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
1715
explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1716
id select_type table type possible_keys key key_len ref rows Extra
1717
1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by; Using temporary; Using filesort
1718
explain select distinct a1,a2,b from t2;
1719
id select_type table type possible_keys key key_len ref rows Extra
1720
1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using index for group-by
1721
explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1722
id select_type table type possible_keys key key_len ref rows Extra
1723
1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by
1724
explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1725
id select_type table type possible_keys key key_len ref rows Extra
1726
1 SIMPLE t2 index NULL idx_t2_1 163 NULL # Using where; Using index
1727
explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1728
id select_type table type possible_keys key key_len ref rows Extra
1729
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
1730
explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1731
id select_type table type possible_keys key key_len ref rows Extra
1732
1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by; Using temporary; Using filesort
1733
select distinct a1,a2,b from t1;
1751
select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1757
select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1760
select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1762
select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1765
select distinct a1,a2,b from t2;
1787
select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1793
select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1796
select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1798
select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1801
explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
1802
id select_type table type possible_keys key key_len ref rows Extra
1803
1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index
1804
explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1805
id select_type table type possible_keys key key_len ref rows Extra
1806
1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index
1807
explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1808
id select_type table type possible_keys key key_len ref rows filtered Extra
1809
1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index
1811
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"."b" = 'c') and ("test"."t1"."a1" > 'a') and ("test"."t1"."a2" > 'a'))
1812
explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
1813
id select_type table type possible_keys key key_len ref rows Extra
1814
1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index
1815
explain extended select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
1816
id select_type table type possible_keys key key_len ref rows filtered Extra
1817
1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index
1819
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'))
1820
select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
1821
count(distinct a1,a2,b)
1823
select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1824
count(distinct a1,a2,b,c)
1826
select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1827
count(distinct a1,a2,b)
1829
select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
1832
select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
1833
ord(a1) + count(distinct a1,a2,b)
1835
explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
1836
id select_type table type possible_keys key key_len ref rows Extra
1837
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
1838
explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
1839
id select_type table type possible_keys key key_len ref rows Extra
1840
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
1841
explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
1842
id select_type table type possible_keys key key_len ref rows Extra
1843
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
1844
explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
1845
id select_type table type possible_keys key key_len ref rows Extra
1846
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
1847
explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
1848
id select_type table type possible_keys key key_len ref rows Extra
1849
1 SIMPLE t1 range NULL idx_t1_1 147 NULL 9 Using index for group-by
1850
select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
1851
a1 a2 b concat(min(c), max(c))
1864
select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
1878
select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
1879
concat(a1,min(c)) b max(c)
1892
select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
1893
concat(a1,a2) b min(c) max(c)
1906
select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
1907
concat(ord(min(b)),ord(max(b))) min(b) max(b)
1916
explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b;
1917
id select_type table type possible_keys key key_len ref rows Extra
1918
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort
1919
explain select a1,a2,b,d from t1 group by a1,a2,b;
1920
id select_type table type possible_keys key key_len ref rows Extra
1921
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort
1922
explain extended select a1,a2,min(b),max(b) from t1
1923
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
1924
id select_type table type possible_keys key key_len ref rows filtered Extra
1925
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 76 85.53 Using where; Using index
1927
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"
1928
explain extended select a1,a2,b,min(c),max(c) from t1
1929
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
1930
id select_type table type possible_keys key key_len ref rows filtered Extra
1931
1 SIMPLE t1 ALL idx_t1_0,idx_t1_1,idx_t1_2 NULL NULL NULL 128 50.78 Using where; Using temporary; Using filesort
1933
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"
1934
explain extended select a1,a2,b,c from t1
1935
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
1936
id select_type table type possible_keys key key_len ref rows filtered Extra
1937
1 SIMPLE t1 ALL idx_t1_0,idx_t1_1,idx_t1_2 NULL NULL NULL 128 50.78 Using where; Using temporary; Using filesort
1939
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"
1940
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
1941
id select_type table type possible_keys key key_len ref rows Extra
1942
1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
1943
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;
1944
id select_type table type possible_keys key key_len ref rows filtered Extra
1945
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 76 85.53 Using where; Using index
1947
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"
1948
explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
1949
id select_type table type possible_keys key key_len ref rows Extra
1950
1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
1951
select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
1954
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1;
1955
id select_type table type possible_keys key key_len ref rows Extra
1956
1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
1957
explain select a1,a2,b,min(c),max(c) from t2
1958
where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b;
1959
id select_type table type possible_keys key key_len ref rows Extra
1960
1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
1961
explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;
1962
id select_type table type possible_keys key key_len ref rows Extra
1963
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort
1964
explain select a1,a2,count(a2) from t1 group by a1,a2,b;
1965
id select_type table type possible_keys key key_len ref rows Extra
1966
1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using index
1967
explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
1968
id select_type table type possible_keys key key_len ref rows filtered Extra
1969
1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index
1971
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"
1972
explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
1973
id select_type table type possible_keys key key_len ref rows filtered Extra
1974
1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index
1976
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"
1977
explain select distinct(a1) from t1 where ord(a2) = 98;
1978
id select_type table type possible_keys key key_len ref rows Extra
1979
1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index
1980
select distinct(a1) from t1 where ord(a2) = 98;
1986
explain select a1 from t1 where a2 = 'b' group by a1;
1987
id select_type table type possible_keys key key_len ref rows Extra
1988
1 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using where; Using index for group-by
1989
select a1 from t1 where a2 = 'b' group by a1;
1995
explain select distinct a1 from t1 where a2 = 'b';
1996
id select_type table type possible_keys key key_len ref rows Extra
1997
1 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using where; Using index for group-by
1998
select distinct a1 from t1 where a2 = 'b';
2004
drop table t1,t2,t3;
2005
create table t1 (c1 int not null,c2 int not null, primary key(c1,c2));
2006
insert into t1 (c1,c2) values
2007
(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);
2008
select distinct c1, c2 from t1 order by c2;
2019
select c1,min(c2) as c2 from t1 group by c1 order by c2;
2024
select c1,c2 from t1 group by c1,c2 order by c2;
2036
CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b));
2037
INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
2039
Table Op Msg_type Msg_text
2040
test.t1 optimize status OK
2041
SELECT a FROM t1 WHERE a='AA' GROUP BY a;
2044
SELECT a FROM t1 WHERE a='BB' GROUP BY a;
2047
EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;
2048
id select_type table type possible_keys key key_len ref rows Extra
2049
1 SIMPLE t1 ref PRIMARY PRIMARY 7 const 3 Using where; Using index
2050
EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;
2051
id select_type table type possible_keys key key_len ref rows Extra
2052
1 SIMPLE t1 ref PRIMARY PRIMARY 7 const 1 Using where; Using index
2053
SELECT DISTINCT a FROM t1 WHERE a='BB';
2056
SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
2059
SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;
2063
CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a));
2064
INSERT INTO t1 (a) VALUES
2065
(''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
2066
('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'),
2067
('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');
2068
EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;
2069
id select_type table type possible_keys key key_len ref rows Extra
2070
1 SIMPLE t1 range NULL PRIMARY 66 NULL 12 Using index for group-by
2071
SELECT DISTINCT a,a FROM t1 ORDER BY a;
2076
GREATER LONDON GREATER LONDON
2077
NORTH CENTRAL NORTH CENTRAL
2078
NORTH EAST NORTH EAST
2079
NORTH WEST NORTH WEST
2081
SOUTH EAST SOUTH EAST
2082
SOUTH WEST SOUTH WEST
2085
CREATE TABLE t1 (id1 INT, id2 INT);
2086
CREATE TABLE t2 (id2 INT, id3 INT, id5 INT);
2087
CREATE TABLE t3 (id3 INT, id4 INT);
2088
CREATE TABLE t4 (id4 INT);
2089
CREATE TABLE t5 (id5 INT, id6 INT);
2090
CREATE TABLE t6 (id6 INT);
2091
INSERT INTO t1 VALUES(1,1);
2092
INSERT INTO t2 VALUES(1,1,1);
2093
INSERT INTO t3 VALUES(1,1);
2094
INSERT INTO t4 VALUES(1);
2095
INSERT INTO t5 VALUES(1,1);
2096
INSERT INTO t6 VALUES(1);
2100
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
2101
ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
2102
id2 id1 id3 id5 id4 id3 id6 id5
2107
(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2
2108
ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
2109
id2 id1 id4 id3 id6 id5 id3 id5
2111
SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2);
2112
id2 id1 id3 id4 id6 id5 id3 id5
2115
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
2116
ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5))
2119
id2 id3 id5 id4 id3 id6 id5 id1
2122
(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6)))
2125
id2 id3 id5 id4 id3 id6 id5 id1
2127
DROP TABLE t1,t2,t3,t4,t5,t6;
2128
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
2129
INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
2130
explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
2131
id select_type table type possible_keys key key_len ref rows Extra
2132
1 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 1 Using where; Using index for group-by
2133
SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
2136
SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
2139
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
2140
INSERT INTO t2 SELECT a,b,b FROM t1;
2141
explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
2142
id select_type table type possible_keys key key_len ref rows Extra
2143
1 SIMPLE t2 range PRIMARY PRIMARY 12 NULL 1 Using where; Using index for group-by
2144
SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
2148
CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
2149
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
2150
(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
2151
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
2152
id select_type table type possible_keys key key_len ref rows Extra
2153
1 SIMPLE t1 range NULL a 5 NULL 8 Using index for group-by
2155
SELECT max(b), a FROM t1 GROUP BY a;
2161
SHOW STATUS LIKE 'handler_read__e%';
2165
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
2166
id select_type table type possible_keys key key_len ref rows Extra
2167
1 SIMPLE t1 range NULL a 5 NULL 8 Using index for group-by
2169
CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
2170
SHOW STATUS LIKE 'handler_read__e%';
2175
SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
2181
SHOW STATUS LIKE 'handler_read__e%';
2186
(SELECT max(b), a FROM t1 GROUP BY a) UNION
2187
(SELECT max(b), a FROM t1 GROUP BY a);
2193
SHOW STATUS LIKE 'handler_read__e%';
2197
EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION
2198
(SELECT max(b), a FROM t1 GROUP BY a);
2199
id select_type table type possible_keys key key_len ref rows Extra
2200
1 PRIMARY t1 range NULL a 5 NULL 8 Using index for group-by
2201
2 UNION t1 range NULL a 5 NULL 8 Using index for group-by
2202
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
2203
EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
2204
FROM t1 AS t1_outer;
2205
id select_type table type possible_keys key key_len ref rows Extra
2206
1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index
2207
2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
2208
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS
2209
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2210
id select_type table type possible_keys key key_len ref rows Extra
2211
1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index
2212
2 SUBQUERY t1 index NULL a 10 NULL 15 Using index
2213
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
2214
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
2215
id select_type table type possible_keys key key_len ref rows Extra
2216
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2217
2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
2218
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
2219
a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2220
id select_type table type possible_keys key key_len ref rows Extra
2221
1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index
2222
2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
2223
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
2224
a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2225
id select_type table type possible_keys key key_len ref rows Extra
2226
1 PRIMARY t1_outer range NULL a 5 NULL 8 Using index for group-by
2227
2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
2228
EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2
2229
ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2)
2230
AND t1_outer1.b = t1_outer2.b;
2231
id select_type table type possible_keys key key_len ref rows Extra
2232
1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index
2233
1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer
2234
2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
2235
EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
2236
FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
2237
id select_type table type possible_keys key key_len ref rows Extra
2238
1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using index
2239
2 SUBQUERY t1_outer index NULL a 10 NULL 15 Using index
2240
3 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
2241
CREATE TABLE t3 LIKE t1;
2243
INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
2244
SHOW STATUS LIKE 'handler_read__e%';
2250
INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2)
2252
SHOW STATUS LIKE 'handler_read__e%';
2257
DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
2258
SHOW STATUS LIKE 'handler_read__e%';
2263
DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x
2265
ERROR 21000: Subquery returns more than 1 row
2266
SHOW STATUS LIKE 'handler_read__e%';
2270
DROP TABLE t1,t2,t3;
2271
CREATE TABLE t1 (a int, INDEX idx(a));
2272
INSERT INTO t1 VALUES
2273
(4), (2), (1), (2), (4), (2), (1), (4),
2274
(4), (2), (1), (2), (2), (4), (1), (4);
2275
EXPLAIN SELECT DISTINCT(a) FROM t1;
2276
id select_type table type possible_keys key key_len ref rows Extra
2277
1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by
2278
SELECT DISTINCT(a) FROM t1;
2283
EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
2284
id select_type table type possible_keys key key_len ref rows Extra
2285
1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by
2286
SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
2292
CREATE TABLE t1 (a INT, b INT);
2293
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
2294
INSERT INTO t1 SELECT a + 1, b FROM t1;
2295
INSERT INTO t1 SELECT a + 2, b FROM t1;
2297
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2298
id select_type table type possible_keys key key_len ref rows Extra
2299
1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort
2300
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2306
CREATE INDEX break_it ON t1 (a, b);
2308
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
2309
id select_type table type possible_keys key key_len ref rows Extra
2310
1 SIMPLE t1 range NULL break_it 10 NULL 7 Using index for group-by
2311
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
2318
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2319
id select_type table type possible_keys key key_len ref rows Extra
2320
1 SIMPLE t1 range NULL break_it 10 NULL 7 Using index for group-by; Using temporary; Using filesort
2321
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2328
SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
2329
id select_type table type possible_keys key key_len ref rows Extra
2330
1 SIMPLE t1 index NULL break_it 10 NULL 12 Using index
2331
SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
2332
a MIN(b) MAX(b) AVG(b)
added
removed
mysql-test/r/group_min_max.result
