~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/r/group_min_max.result

Committer: Lee
Date: 2008-12-19 23:29:43 UTC
mfrom: (724.1.1 testable)
mto: (754.1.1 devel) (758.1.3 devel)
mto: This revision was merged to the branch mainline in revision 732.
Revision ID: lbieber@lbieber-desktop-20081219232943-s9q9hu1tc0pqp8ol

fix variables_c test, remove have_community checks, no longer valid

files added:
tests/r/flush.result

files removed:
tests/include/have_community_features.inc

tests/suite/broken/r/have_community_features.require

files renamed:
tests/suite/broken/r/group_min_max.result => tests/r/group_min_max.result

tests/suite/broken/r/variables_c.result => tests/r/variables_c.result

tests/suite/broken/t/group_min_max.test => tests/t/group_min_max.test

tests/suite/broken/t/variables_c.test => tests/t/variables_c.test

files modified:
drizzled/opt_range.cc

Show diffs side-by-side

added added

removed removed

tests/r/group_min_max.result

drop table if exists t1;

create table t1 (

a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '

);

) ENGINE=MYISAM;

insert into t1 (a1, a2, b, c, d) values

('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),

('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),

drop table if exists t2;

create table t2 (

a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' '

);

) ENGINE=MYISAM;

insert into t2 select * from t1;

insert into t2 (a1, a2, b, c, d) values

('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),

drop table if exists t3;

create table t3 (

a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' '

);

) ENGINE=MYISAM;

insert into t3 (a1, a2, b, c, d) values

('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),

('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),

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

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 65 NULL 5 Using index for group-by

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 130 NULL 5 Using index for group-by

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 147 NULL 17 Using index for group-by

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 147 NULL 17 Using index for group-by

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 range NULL idx_t2_1 # NULL # Using index for group-by

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 130 NULL 5 Using index for group-by

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 147 NULL 17 Using index for group-by

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 130 NULL 5 Using index for group-by

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 147 NULL 17 Using index for group-by

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

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 147 NULL 10 Using where; Using index for group-by

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 147 NULL 14 Using where; Using index for group-by

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 147 NULL 14 Using where; Using index for group-by

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 147 NULL 14 Using where; Using index for group-by

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 147 NULL 17 Using where; Using index for group-by

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 147 NULL 10 Using where; Using index for group-by

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 147 NULL 10 Using where; Using index for group-by

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 147 NULL 10 Using where; Using index for group-by

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 147 NULL 10 Using where; Using index for group-by

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 147 NULL 14 Using where; Using index for group-by

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 147 NULL 14 Using where; Using index for group-by

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 146 NULL # Using where; Using index for group-by

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 163 NULL # Using where; Using index for group-by

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 163 NULL # Using where; Using index for group-by

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 146 NULL # Using where; Using index for group-by

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 146 NULL # Using where; Using index for group-by

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 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by

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 146 NULL # Using where; Using index for group-by

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 163 NULL # Using where; Using index for group-by

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 146 NULL # Using where; Using index for group-by

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 163 NULL # Using where; Using index for group-by

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 163 NULL # Using where; Using index for group-by

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 146 NULL # Using where; Using index for group-by

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

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 147 NULL 5 Using where; Using index for group-by

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 147 NULL 5 Using where; Using index for group-by

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 147 NULL 9 Using where; Using index for group-by

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 147 NULL 9 Using where; Using index for group-by

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 147 NULL 9 Using where; Using index for group-by

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 163 NULL 5 Using where; Using index for group-by

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 163 NULL 5 Using where; Using index for group-by

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 146 NULL 10 Using where; Using index for group-by

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 163 NULL 10 Using where; Using index for group-by

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 146 NULL 10 Using where; Using index for group-by

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 6 NULL 4 Using where; Using index for group-by

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 6 NULL 4 Using where; Using index for group-by

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

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 163 NULL 5 Using where; Using index for group-by

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 146 NULL 5 Using where; Using index for group-by

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 163 NULL 10 Using where; Using index for group-by

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 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

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

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 147 NULL # Using where; Using index for group-by

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 163 NULL 17 Using where; Using index for group-by

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 147 NULL 17 Using where; Using index for group-by

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 163 NULL 17 Using where; Using index for group-by

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 163 NULL 17 Using where; Using index for group-by

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 163 NULL 17 Using where; Using index for group-by

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 163 NULL 17 Using where; Using index for group-by

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 163 NULL 17 Using where; Using index for group-by

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 163 NULL 17 Using where; Using index for group-by

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 163 NULL 17 Using where; Using index for group-by

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 147 NULL 17 Using where; Using index for group-by

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 147 NULL 17 Using where; Using index for group-by

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 163 NULL 17 Using where; Using index for group-by

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 163 NULL 17 Using where; Using index for group-by

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 163 NULL 17 Using where; Using index for group-by

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 163 NULL 17 Using where; Using index for group-by

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 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by

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 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by

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,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;

959

a1 a2 b min(c) max(c)

960

a a a b111 d111

961

a a b e112 h112

962

a b a i121 l121

963

a b b m122 p122

964

b a a b211 d211

965

b a b e212 h212

966

b b a i221 l221

967

b b b m222 p222

968

c a a b311 d311

969

c a b e312 h312

970

c b a i321 l321

971

c b b m322 p322

972

d a a b411 d411

973

d a b e412 h412

974

d b a i421 l421

975

d b b m422 p422

976

942

select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b;

977

943

a1 a2 b max(c)

978

a a b h112

979

a b a l121

980

a b b p122

981

b a b h212

982

b b a l221

983

b b b p222

984

c a b h312

985

c b a l321

986

c b b p322

987

d a b h412

988

d b a l421

989

d b b p422

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)

992

a a b g112 h112

993

a b a i121 l121

994

a b b m122 p122

995

b a b f212 h212

996

b b a i221 l221

997

b b b m222 p222

998

c a b f312 h312

999

c b a i321 l321

1000

c b b m322 p322

1001

d a b f412 h412

1002

d b a i421 l421

1003

d b b m422 p422

1004

944

select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b;

1005

945

a1 a2 b max(c)

1006

946

select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;

1021

961

d b a j421

1022

962

select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;

1023

963

a1 a2 b min(c) max(c)

1024

a a a a111 d111

1025

a a b e112 h112

1026

a b a i121 k121

1027

b a a a211 d211

1028

b a b e212 h212

1029

b b a i221 k221

1030

c a a a311 d311

1031

c a b e312 h312

1032

c b a i321 j321

1033

d a a a411 d411

1034

d a b e412 h412

1035

d b a i421 j421

1036

964

select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;

1037

965

a1 a2 b max(c)

1038

a a a d111

1039

a a b h112

1040

a b a l121

1041

a b b p122

1042

b a a d211

1043

b a b h212

1044

b b a l221

1045

b b b p222

1046

c a a d311

1047

c a b h312

1048

c b a l321

1049

c b b p322

1050

d a a d411

1051

d a b h412

1052

d b a l421

1053

d b b p422

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)

1056

a a a b111 d111

1057

a a b e112 h112

1058

a b a i121 l121

1059

a b b m122 p122

1060

b a a b211 d211

1061

b a b e212 h212

1062

b b a i221 l221

1063

b b b m222 p222

1064

c a a b311 d311

1065

c a b e312 h312

1066

c b a i321 l321

1067

c b b m322 p322

1068

d a a b411 d411

1069

d a b e412 h412

1070

d b a i421 l421

1071

d b b m422 p422

1072

966

select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;

1073

967

a1 a2 b max(c)

1074

968

a a a d111

1107

1001

d b b m422 p422

1108

1002

select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;

1109

1003

a1 a2 b min(c) max(c)

1110

a a a c111 d111

1111

a a b e112 g112

1112

b a a b211 d211

1113

b a b e212 f212

1114

c a a b311 d311

1115

c a b e312 f312

1116

d a a b411 d411

1117

d a b e412 f412

1118

1004

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

1005

a1 a2 b min(c) max(c)

1120

a a a a111 c111

1121

b a a a211 c211

1122

c a a a311 c311

1123

d a a a411 c411

1124

1006

d a b g412 g412

1125

1007

d b a k421 k421

1126

1008

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

1009

a1 a2 b min(c) max(c)

1128

a a a c111 d111

1129

a a b e112 h112

1130

b a a b211 d211

1131

b a b e212 h212

1132

c a a b311 d311

1133

c a b e312 h312

1134

d a a b411 d411

1135

d a b e412 h412

1136

1010

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

1011

a1 a2 b min(c) max(c)

1138

a a a b111 d111

1139

a a b e112 h112

1140

b a a b211 d211

1141

b a b e212 h212

1142

c a a b311 d311

1143

c a b e312 h312

1144

d a a b411 d411

1145

d a b e412 h412

1146

1012

select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b;

1147

1013

a1 a2 b max(c)

1148

1014

a a a d111

1358

1224

where exists ( select * from t2 where t2.c = t1.c )

1359

1225

group by a1,a2,b;

1360

1226

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

1227

1 PRIMARY t1 index NULL idx_t1_1 651 NULL 128 Using where; Using index

1228

2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 651 NULL 164 Using where; Using index

1363

1229

explain select a1,a2,b,min(c),max(c) from t1

1364

1230

where exists ( select * from t2 where t2.c > 'b1' )

1365

1231

group by a1,a2,b;

1366

1232

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

1233

1 PRIMARY t1 range NULL idx_t1_1 585 NULL 17 Using index for group-by

1234

2 SUBQUERY t2 index NULL idx_t2_1 651 NULL 164 Using where; Using index

1369

1235

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

1236

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

1237

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

1372

1238

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

1239

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

1240

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

1375

1241

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

1242

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

1243

1 SIMPLE t1 range NULL idx_t1_1 651 NULL 17 Using where; Using index for group-by

1378

1244

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

1245

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

1246

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

1381

1247

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

1248

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

1249

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

1384

1250

explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;

1385

1251

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

1252

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

1387

1253

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

1254

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

1255

1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using where; Using index for group-by

1390

1256

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

1257

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

1258

1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 651 NULL # Using where; Using index

1393

1259

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

1260

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

1261

1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 651 NULL # Using where; Using index

1396

1262

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

1263

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

1264

1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index

1399

1265

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

1266

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

1267

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

1402

1268

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

1269

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

1270

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

1405

1271

explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;

1406

1272

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

1273

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

1408

1274

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

1275

a1 a2 b min(c) max(c)

1410

1276

a a b e112 h112

1413

1279

c b b m322 p322

1414

1280

d a b e412 h412

1415

1281

d b b m422 p422

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)

1418

a a a c111 d111

1419

a a b e112 h112

1420

b a a b211 d211

1421

b a b e212 h212

1422

c a a b311 d311

1423

c a b e312 h312

1424

c b a i321 l321

1425

c b b m322 p322

1426

d a a b411 d411

1427

d a b e412 h412

1428

d b a i421 l421

1429

d b b m422 p422

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)

1432

a b a i121 l121

1433

b b a i221 l221

1434

c b a i321 l321

1435

d b a i421 l421

1436

1282

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;

1437

1283

a1 a2 b min(c)

1438

b b a k221

1439

c b a k321

1440

d b a k421

1441

1284

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;

1442

1285

a1 a2 b min(c)

1443

b b a k221

1444

c b a k321

1445

d b a k421

1446

1286

select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;

1447

1287

a1 a2 b min(c)

1448

1288

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;

1479

1319

d b a i421 l421

1480

1320

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;

1481

1321

a1 a2 b min(c)

1482

b b a k221

1483

c b a k321

1484

d b a k421

1485

1322

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;

1486

1323

a1 a2 b min(c)

1487

b b a k221

1488

c b a k321

1489

d b a k421

1490

1324

select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;

1491

1325

a1 a2 b min(c)

1492

1326

explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;

1493

1327

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

1328

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

1495

1329

explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;

1496

1330

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

1331

1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using where; Using index for group-by

1498

1332

explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;

1499

1333

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

1334

1 SIMPLE t1 index NULL idx_t1_1 651 NULL 128 Using where; Using index

1501

1335

explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;

1502

1336

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

1337

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

1504

1338

explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;

1505

1339

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

1340

1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_2 584 NULL # Using where; Using index

1507

1341

explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;

1508

1342

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

1343

1 SIMPLE t2 index NULL idx_t2_2 584 NULL # Using where; Using index

1510

1344

explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;

1511

1345

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

1346

1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index

1513

1347

explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;

1514

1348

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

1349

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

1350

select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;

1517

1351

a1 a2 b

1518

1352

a a b

1554

1388

a1 a2 b

1555

1389

explain select distinct a1,a2,b from t1;

1556

1390

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

1391

1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using index for group-by

1558

1392

explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');

1559

1393

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

1394

1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using where; Using index for group-by

1561

1395

explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');

1562

1396

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

1397

1 SIMPLE t1 index NULL idx_t1_1 651 NULL 128 50.78 Using where; Using index

1564

1398

Warnings:

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'))

1399

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'))

1566

1400

explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');

1567

1401

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

1402

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

1569

1403

explain select distinct b from t1 where (a2 >= 'b') and (b = 'a');

1570

1404

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

1405

1 SIMPLE t1 index NULL idx_t1_2 585 NULL 128 Using where; Using index

1572

1406

explain select distinct a1,a2,b from t2;

1573

1407

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

1408

1 SIMPLE t2 index NULL idx_t2_2 584 NULL # Using index

1575

1409

explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');

1576

1410

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

1411

1 SIMPLE t2 index NULL idx_t2_2 584 NULL # Using where; Using index

1578

1412

explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');

1579

1413

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

1414

1 SIMPLE t2 index NULL idx_t2_1 651 NULL 164 50.61 Using where; Using index

1581

1415

Warnings:

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'))

1416

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'))

1583

1417

explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');

1584

1418

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

1419

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

1586

1420

explain select distinct b from t2 where (a2 >= 'b') and (b = 'a');

1587

1421

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

1422

1 SIMPLE t2 index NULL idx_t2_2 584 NULL 164 Using where; Using index

1589

1423

select distinct a1,a2,b from t1;

1590

1424

a1 a2 b

1591

1425

a a a

1702

1536

d e

1703

1537

explain select distinct a1,a2,b from t1;

1704

1538

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

1539

1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using index for group-by

1706

1540

explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;

1707

1541

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

1542

1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using where; Using index for group-by

1709

1543

explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;

1710

1544

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

1545

1 SIMPLE t1 index NULL idx_t1_1 651 NULL 128 Using where; Using index

1712

1546

explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;

1713

1547

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

1548

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

1715

1549

explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;

1716

1550

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

1551

1 SIMPLE t1 range NULL idx_t1_1 585 NULL 17 Using where; Using index for group-by; Using temporary; Using filesort

1718

1552

explain select distinct a1,a2,b from t2;

1719

1553

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

1554

1 SIMPLE t2 index NULL idx_t2_2 584 NULL # Using index

1721

1555

explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;

1722

1556

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

1557

1 SIMPLE t2 index NULL idx_t2_2 584 NULL # Using where; Using index

1724

1558

explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;

1725

1559

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

1560

1 SIMPLE t2 index NULL idx_t2_1 651 NULL # Using where; Using index

1727

1561

explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;

1728

1562

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

1563

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

1730

1564

explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;

1731

1565

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

1566

1 SIMPLE t2 index NULL idx_t2_2 584 NULL # Using where; Using index; Using temporary; Using filesort

1733

1567

select distinct a1,a2,b from t1;

1734

1568

a1 a2 b

1735

1569

a a a

1800

1634

1801

1635

explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');

1802

1636

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

1637

1 SIMPLE t1 index NULL idx_t1_2 585 NULL 128 Using where; Using index

1804

1638

explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');

1805

1639

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

1640

1 SIMPLE t1 index NULL idx_t1_1 651 NULL 128 Using where; Using index

1807

1641

explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');

1808

1642

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

1643

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

1810

1644

Warnings:

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'))

1645

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'))

1812

1646

explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');

1813

1647

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

1648

1 SIMPLE t1 index NULL idx_t1_2 585 NULL 128 Using where; Using index

1815

1649

explain extended select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');

1816

1650

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

1651

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

1818

1652

Warnings:

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'))

1653

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

1654

select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');

1821

1655

count(distinct a1,a2,b)

1822

1656

1834

1668

104

1835

1669

explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;

1836

1670

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

1671

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

1838

1672

explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;

1839

1673

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

1674

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

1841

1675

explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;

1842

1676

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

1677

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

1844

1678

explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;

1845

1679

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

1680

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

1847

1681

explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;

1848

1682

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

1683

1 SIMPLE t1 range NULL idx_t1_1 585 NULL 9 Using index for group-by

1850

1684

select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;

1851

1685

a1 a2 b concat(min(c), max(c))

1852

1686

a a a a111d111

1922

1756

explain extended select a1,a2,min(b),max(b) from t1

1923

1757

where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;

1924

1758

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

1759

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

1926

1760

Warnings:

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"

1761

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

1762

explain extended select a1,a2,b,min(c),max(c) from t1

1929

1763

where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;

1930

1764

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

1765

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

1932

1766

Warnings:

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"

1767

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

1768

explain extended select a1,a2,b,c from t1

1935

1769

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

1770

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

1771

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

1938

1772

Warnings:

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"

1773

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

1774

explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;

1941

1775

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

1776

1 SIMPLE t2 index NULL idx_t2_1 651 NULL 164 Using where; Using index

1943

1777

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

1778

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

1779

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

1946

1780

Warnings:

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"

1781

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

1782

explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;

1949

1783

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

1784

1 SIMPLE t2 index NULL idx_t2_1 651 NULL 164 Using where; Using index

1951

1785

select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;

1952

1786

a1 a2 min(b) c

1953

1787

a a a a111

1954

1788

explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1;

1955

1789

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

1790

1 SIMPLE t2 index NULL idx_t2_1 651 NULL 164 Using where; Using index

1957

1791

explain select a1,a2,b,min(c),max(c) from t2

1958

1792

where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b;

1959

1793

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

1794

1 SIMPLE t2 index NULL idx_t2_1 651 NULL 164 Using where; Using index

1961

1795

explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;

1962

1796

id select_type table type possible_keys key key_len ref rows Extra

1963

1797

1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort

1964

1798

explain select a1,a2,count(a2) from t1 group by a1,a2,b;

1965

1799

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

1800

1 SIMPLE t1 index NULL idx_t1_2 585 NULL 128 Using index

1967

1801

explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;

1968

1802

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

1803

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

1970

1804

Warnings:

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"

1805

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

1806

explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;

1973

1807

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

1808

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

1975

1809

Warnings:

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"

1810

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

1811

explain select distinct(a1) from t1 where ord(a2) = 98;

1978

1812

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

1813

1 SIMPLE t1 index NULL idx_t1_2 585 NULL 128 Using where; Using index

1980

1814

select distinct(a1) from t1 where ord(a2) = 98;

1981

1815

1982

1816

1985

1819

1986

1820

explain select a1 from t1 where a2 = 'b' group by a1;

1987

1821

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

1822

1 SIMPLE t1 range NULL idx_t1_1 518 NULL 5 Using where; Using index for group-by

1989

1823

select a1 from t1 where a2 = 'b' group by a1;

1990

1824

1991

1825

1994

1828

1995

1829

explain select distinct a1 from t1 where a2 = 'b';

1996

1830

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

1831

1 SIMPLE t1 range NULL idx_t1_1 518 NULL 5 Using where; Using index for group-by

1998

1832

select distinct a1 from t1 where a2 = 'b';

1999

1833

2000

1834

2002

1836

2003

1837

2004

1838

drop table t1,t2,t3;

2005

create table t1 (c1 int not null,c2 int not null, primary key(c1,c2));

1839

create table t1 (c1 int not null,c2 int not null, primary key(c1,c2)) ENGINE=MYISAM;

2006

1840

insert into t1 (c1,c2) values

2007

1841

(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);

2008

1842

select distinct c1, c2 from t1 order by c2;

2033

1867

30 8

2034

1868

30 9

2035

1869

drop table t1;

2036

CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b));

1870

CREATE TABLE t1 (a varchar(5), b int, PRIMARY KEY (a,b));

2037

1871

INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);

2038

1872

OPTIMIZE TABLE t1;

2039

1873

Table Op Msg_type Msg_text

2046

1880

2047

1881

EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;

2048

1882

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

1883

1 SIMPLE t1 ref PRIMARY PRIMARY 22 const 2 Using where; Using index

2050

1884

EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;

2051

1885

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

1886

1 SIMPLE t1 ref PRIMARY PRIMARY 22 const 1 Using where; Using index

2053

1887

SELECT DISTINCT a FROM t1 WHERE a='BB';

2054

1888

2055

1889

2067

1901

('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');

2068

1902

EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;

2069

1903

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

1904

1 SIMPLE t1 range NULL PRIMARY 258 NULL 12 Using index for group-by

2071

1905

SELECT DISTINCT a,a FROM t1 ORDER BY a;

2072

1906

a a

2073

1907

2150

1984

(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);

2151

1985

EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;

2152

1986

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

1987

1 SIMPLE t1 index NULL a 10 NULL 15 Using index

2154

1988

FLUSH STATUS;

2155

1989

SELECT max(b), a FROM t1 GROUP BY a;

2156

1990

max(b) a

2160

1994

6 4

2161

1995

SHOW STATUS LIKE 'handler_read__e%';

2162

1996

Variable_name Value

2163

Handler_read_key 8

2164

Handler_read_next 0

1997

Handler_read_key 2

1998

Handler_read_next 15

2165

1999

EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;

2166

2000

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

2001

1 SIMPLE t1 index NULL a 10 NULL 15 Using index

2168

2002

FLUSH STATUS;

2169

2003

CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;

2170

2004

SHOW STATUS LIKE 'handler_read__e%';

2171

2005

Variable_name Value

2172

Handler_read_key 8

2173

Handler_read_next 0

2006

Handler_read_key 2

2007

Handler_read_next 15

2174

2008

FLUSH STATUS;

2175

2009

SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;

2176

2010

max(b) a

2180

2014

6 4

2181

2015

SHOW STATUS LIKE 'handler_read__e%';

2182

2016

Variable_name Value

2183

Handler_read_key 8

2184

Handler_read_next 0

2017

Handler_read_key 2

2018

Handler_read_next 15

2185

2019

FLUSH STATUS;

2186

2020

(SELECT max(b), a FROM t1 GROUP BY a) UNION

2187

2021

(SELECT max(b), a FROM t1 GROUP BY a);

2192

2026

6 4

2193

2027

SHOW STATUS LIKE 'handler_read__e%';

2194

2028

Variable_name Value

2195

Handler_read_key 16

2029

Handler_read_key 22

2196

2030

Handler_read_next 0

2197

2031

EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION

2198

2032

(SELECT max(b), a FROM t1 GROUP BY a);

2199

2033

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

2034

1 PRIMARY t1 range NULL a 5 NULL 3 Using index for group-by

2035

2 UNION t1 range NULL a 5 NULL 3 Using index for group-by

2202

2036

NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL

2203

2037

EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x

2204

2038

FROM t1 AS t1_outer;

2205

2039

id select_type table type possible_keys key key_len ref rows Extra

2206

2040

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

2041

2 SUBQUERY t1 range NULL a 5 NULL 3 Using index for group-by

2208

2042

EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS

2209

2043

(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);

2210

2044

id select_type table type possible_keys key key_len ref rows Extra

2214

2048

(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;

2215

2049

id select_type table type possible_keys key key_len ref rows Extra

2216

2050

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

2051

2 SUBQUERY t1 range NULL a 5 NULL 3 Using index for group-by

2218

2052

EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE

2219

2053

a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);

2220

2054

id select_type table type possible_keys key key_len ref rows Extra

2221

2055

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

2056

2 SUBQUERY t1 range NULL a 5 NULL 3 Using index for group-by

2223

2057

EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING

2224

2058

a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);

2225

2059

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

2060

1 PRIMARY t1_outer range NULL a 5 NULL 3 Using index for group-by

2061

2 SUBQUERY t1 range NULL a 5 NULL 3 Using index for group-by

2228

2062

EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2

2229

2063

ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2)

2230

2064

AND t1_outer1.b = t1_outer2.b;

2231

2065

id select_type table type possible_keys key key_len ref rows Extra

2232

2066

1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index

2233

2067

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

2068

2 SUBQUERY t1 range NULL a 5 NULL 3 Using index for group-by

2235

2069

EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x

2236

2070

FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;

2237

2071

id select_type table type possible_keys key key_len ref rows Extra

2238

2072

1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using index

2239

2073

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

2074

3 SUBQUERY t1 range NULL a 5 NULL 3 Using index for group-by

2241

2075

CREATE TABLE t3 LIKE t1;

2242

2076

FLUSH STATUS;

2243

2077

INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;

2244

2078

SHOW STATUS LIKE 'handler_read__e%';

2245

2079

Variable_name Value

2246

Handler_read_key 8

2080

Handler_read_key 11

2247

2081

Handler_read_next 0

2248

2082

DELETE FROM t3;

2249

2083

FLUSH STATUS;

2251

2085

FROM t1 LIMIT 1;

2252

2086

SHOW STATUS LIKE 'handler_read__e%';

2253

2087

Variable_name Value

2254

Handler_read_key 8

2088

Handler_read_key 13

2255

2089

Handler_read_next 0

2256

2090

FLUSH STATUS;

2257

2091

DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;

2258

2092

SHOW STATUS LIKE 'handler_read__e%';

2259

2093

Variable_name Value

2260

Handler_read_key 8

2094

Handler_read_key 11

2261

2095

Handler_read_next 0

2262

2096

FLUSH STATUS;

2263

2097

DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x

2265

2099

ERROR 21000: Subquery returns more than 1 row

2266

2100

SHOW STATUS LIKE 'handler_read__e%';

2267

2101

Variable_name Value

2268

Handler_read_key 8

2102

Handler_read_key 13

2269

2103

Handler_read_next 1

2270

2104

DROP TABLE t1,t2,t3;

2271

2105

CREATE TABLE t1 (a int, INDEX idx(a));

2274

2108

(4), (2), (1), (2), (2), (4), (1), (4);

2275

2109

EXPLAIN SELECT DISTINCT(a) FROM t1;

2276

2110

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

2111

1 SIMPLE t1 index NULL idx 5 NULL 16 Using index

2278

2112

SELECT DISTINCT(a) FROM t1;

2279

2113

2280

2114

2282

2116

2283

2117

EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;

2284

2118

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

2119

1 SIMPLE t1 index NULL idx 5 NULL 16 Using index

2286

2120

SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;

2287

2121

2288

2122

2307

2141

EXPLAIN

2308

2142

SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;

2309

2143

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

2144

1 SIMPLE t1 range NULL break_it 10 NULL 3 Using index for group-by

2311

2145

SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;

2312

2146

a MIN(b) MAX(b)

2313

2147

1 1 3

2317

2151

EXPLAIN

2318

2152

SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;

2319

2153

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

2154

1 SIMPLE t1 range NULL break_it 10 NULL 3 Using index for group-by; Using temporary; Using filesort

2321

2155

SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;

2322

2156

a MIN(b) MAX(b)

2323

2157

4 1 3

Older »