~drizzle-trunk/drizzle/development : revision 201

127

fld3

128

select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%';

129

fld3

130

explain select t2.fld3 from t2 where fld3 = 'honeysuckle';

131

id select_type table type possible_keys key key_len ref rows Extra

132

1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index

133

explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle';

134

id select_type table type possible_keys key key_len ref rows Extra

135

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

136

explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle';

137

id select_type table type possible_keys key key_len ref rows Extra

138

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

139

explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle';

140

id select_type table type possible_keys key key_len ref rows Extra

141

1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index

142

explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle';

143

id select_type table type possible_keys key key_len ref rows Extra

144

1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index

145

explain select fld3 from t2 ignore index (fld3,not_used);

146

ERROR 42000: Key 'not_used' doesn't exist in table 't2'

147

explain select fld3 from t2 use index (not_used);

148

ERROR 42000: Key 'not_used' doesn't exist in table 't2'

149

130

select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;

150

131

fld3

151

132

honeysuckle

152

133

honoring

153

explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;

154

id select_type table type possible_keys key key_len ref rows Extra

155

1 SIMPLE t2 range fld3 fld3 30 NULL 2 Using where; Using index

156

134

select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3;

157

135

fld1 fld3

158

136

148504 Colombo

170

148

fld1

171

149

250501

172

150

250502

173

explain select fld1 from t2 where fld1=250501 or fld1="250502";

174

id select_type table type possible_keys key key_len ref rows Extra

175

1 SIMPLE t2 range fld1 fld1 4 NULL 2 Using where; Using index

176

151

select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;

177

152

fld1

178

153

250501

179

154

250502

180

155

250505

181

156

250601

182

explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;

183

id select_type table type possible_keys key key_len ref rows Extra

184

1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index

185

157

select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';

186

158

fld1 fld3

187

159

012001 flanking

278

250

00

279

251

select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%";

280

252

fld3 period

253

overlay 9410

254

offload 9410

281

255

obliterates 9410

282

offload 9410

256

overestimating 9410

283

257

opaquely 9410

284

258

organizer 9410

285

overestimating 9410

286

overlay 9410

287

259

select distinct fld3 from t2 where companynr = 34 order by fld3;

288

260

fld3

289

261

absentee

358

330

vest

359

331

select distinct fld3 from t2 limit 10;

360

332

fld3

361

abates

362

abiding

363

Abraham

364

abrogating

365

absentee

366

abut

367

accessed

368

accruing

369

accumulating

370

accuracies

333

Omaha

334

breaking

335

Romans

336

intercepted

337

bewilderingly

338

astound

339

admonishing

340

sumac

341

flanking

342

combed

371

343

select distinct fld3 from t2 having fld3 like "A%" limit 10;

372

344

fld3

373

345

abates

448

420

a

449

421

aba

450

422

abi

451

Abr

423

abr

452

424

abs

453

425

abu

454

426

acc

470

442

adj

471

443

select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10;

472

444

a

473

aba

474

abi

475

Abr

476

abs

477

abu

478

acc

479

acq

480

acu

481

Ade

482

adj

445

ast

446

adm

447

ann

448

agi

449

afi

450

amm

451

Aug

452

aud

453

att

454

Ado

483

455

create table t3 (

484

456

period int not null,

485

457

name char(32) not null,

596

568

512

597

569

select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3;

598

570

fld3

599

explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;

600

id select_type table type possible_keys key key_len ref rows Extra

601

1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort

602

1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index

603

explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;

604

id select_type table type possible_keys key key_len ref rows Extra

605

1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort

606

1 SIMPLE t3 ref period period 4 test.t1.period 4181

607

explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;

608

id select_type table type possible_keys key key_len ref rows Extra

609

1 SIMPLE t3 index period period 4 NULL 1

610

1 SIMPLE t1 ref period period 4 test.t3.period 4181

611

explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;

612

id select_type table type possible_keys key key_len ref rows Extra

613

1 SIMPLE t1 index period period 4 NULL 1

614

1 SIMPLE t3 ref period period 4 test.t1.period 4181

615

571

select period from t1;

616

572

period

617

573

9410

623

579

select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;

624

580

fld3 period

625

581

breaking 1001

626

explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period;

627

id select_type table type possible_keys key key_len ref rows Extra

628

1 SIMPLE t2 const fld1 fld1 4 const 1

629

1 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1

630

582

select fld3,period from t2,t1 where companynr*10 = 37*10;

631

583

fld3 period

632

584

breaking 9410

1184

1136

uncovering 9410

1185

1137

chews 9410

1186

1138

appendixes 9410

1187

appendixes 9410

1188

appendixes 9410

1189

appendixes 9410

1190

appendixes 9410

1191

appendixes 9410

1192

1139

raining 9410

1193

1140

infest 9410

1194

1141

compartment 9410

1217

1164

encompasses 9410

1218

1165

presentation 9410

1219

1166

Kantian 9410

1167

appendixes 9410

1168

appendixes 9410

1169

appendixes 9410

1170

appendixes 9410

1171

appendixes 9410

1220

1172

select fld3,period,price,price2 from t2,t3 where t2.fld1=t3.t2nr and period >= 1001 and period <= 1002 and t2.companynr = 37 order by fld3,period, price;

1221

1173

fld3 period price price2

1222

1174

admonishing 1002 28357832 8723648

1355

1307

select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;

1356

1308

count(*)

1357

1309

1199

1358

explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;

1359

id select_type table type possible_keys key key_len ref rows Extra

1360

1 SIMPLE t2 ALL NULL NULL NULL NULL 1200

1361

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists

1362

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;

1363

id select_type table type possible_keys key key_len ref rows Extra

1364

1 SIMPLE t4 ALL NULL NULL NULL NULL 12

1365

1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists

1366

1310

select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;

1367

1311

companynr companyname

1368

1312

select count(*) from t2 left join t4 using (companynr) where companynr is not null;

1369

1313

count(*)

1370

1314

1200

1371

explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;

1372

id select_type table type possible_keys key key_len ref rows Extra

1373

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1374

explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;

1375

id select_type table type possible_keys key key_len ref rows Extra

1376

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1377

1315

delete from t2 where fld1=999999;

1378

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;

1379

id select_type table type possible_keys key key_len ref rows Extra

1380

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1381

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1

1382

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;

1383

id select_type table type possible_keys key key_len ref rows Extra

1384

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1385

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1

1386

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;

1387

id select_type table type possible_keys key key_len ref rows Extra

1388

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1389

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1

1390

explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;

1391

id select_type table type possible_keys key key_len ref rows Extra

1392

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1393

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1394

explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;

1395

id select_type table type possible_keys key key_len ref rows Extra

1396

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1397

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1398

explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;

1399

id select_type table type possible_keys key key_len ref rows Extra

1400

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1401

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1402

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;

1403

id select_type table type possible_keys key key_len ref rows Extra

1404

1 SIMPLE t4 ALL NULL NULL NULL NULL 12

1405

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1406

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;

1407

id select_type table type possible_keys key key_len ref rows Extra

1408

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12

1409

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1410

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;

1411

id select_type table type possible_keys key key_len ref rows Extra

1412

1 SIMPLE t4 ALL NULL NULL NULL NULL 12

1413

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1414

explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;

1415

id select_type table type possible_keys key key_len ref rows Extra

1416

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1417

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1418

explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;

1419

id select_type table type possible_keys key key_len ref rows Extra

1420

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1421

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1422

explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;

1423

id select_type table type possible_keys key key_len ref rows Extra

1424

1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where

1425

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1426

1316

select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;

1427

1317

companynr companynr

1428

1318

37 36

1429

1319

41 40

1430

explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;

1431

id select_type table type possible_keys key key_len ref rows Extra

1432

1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary

1433

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer

1434

1320

select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;

1435

1321

fld1 companynr fld3 period

1436

1322

038008 37 reporters 1008

1502

1388

select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";

1503

1389

count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)

1504

1390

70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069

1505

explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";

1506

id select_type table type possible_keys key key_len ref rows filtered Extra

1507

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where

1508

Warnings:

1509

Note 1003 select count(0) AS "count(*)",min("test"."t2"."fld4") AS "min(fld4)",max("test"."t2"."fld4") AS "max(fld4)",sum("test"."t2"."fld1") AS "sum(fld1)",avg("test"."t2"."fld1") AS "avg(fld1)",std("test"."t2"."fld1") AS "std(fld1)",variance("test"."t2"."fld1") AS "variance(fld1)" from "test"."t2" where (("test"."t2"."companynr" = 34) and ("test"."t2"."fld4" <> ''))

1510

1391

select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;

1511

1392

companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)

1512

1393

00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087

1943

1824

select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1 = t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008 or t3.t2nr = t2.fld1 and t2.fld1 = 38008 group by t2.fld1;

1944

1825

fld1 sum(price)

1945

1826

038008 234298

1946

explain select fld3 from t2 where 1>2 or 2>3;

1947

id select_type table type possible_keys key key_len ref rows Extra

1948

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1949

explain select fld3 from t2 where fld1=fld1;

1950

id select_type table type possible_keys key key_len ref rows Extra

1951

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1952

1827

select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;

1953

1828

companynr fld1

1954

1829

34 250501

1998

1873

select count(*) from t3 where companynr=512 and price2=76234234;

1999

1874

count(*)

2000

1875

4181

2001

explain select min(fld1),max(fld1),count(*) from t2;

2002

id select_type table type possible_keys key key_len ref rows Extra

2003

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2004

1876

select min(fld1),max(fld1),count(*) from t2;

2005

1877

min(fld1) max(fld1) count(*)

2006

1878

0 1232609 1199

2084

1956

fld6 char(4) latin1_swedish_ci NO NULL #

2085

1957

show full columns from t2 from test like 's%';

2086

1958

Field Type Collation Null Key Default Extra Privileges Comment

2087

show keys from t2;

2088

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

2089

t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE

2090

t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE

2091

t2 1 fld3 1 fld3 A NULL NULL NULL BTREE

2092

1959

drop table t4, t3, t2, t1;

2093

1960

CREATE TABLE t1 (

2094

1961

id bigint(8) unsigned NOT NULL auto_increment,

2189

2056

select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;

2190

2057

a a

2191

2058

1 2

2059

1 3

2192

2060

2 2

2061

2 3

2193

2062

3 2

2194

1 3

2195

2 3

2196

2063

3 3

2197

2064

select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;

2198

2065

a a

2317

2184

insert into t1 values (2);

2318

2185

insert into t2 values (1);

2319

2186

insert into t4 values (1,1);

2320

explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3

2321

left join t4 on id3 = id4 where id2 = 1 or id4 = 1;

2322

id select_type table type possible_keys key key_len ref rows Extra

2323

1 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found

2324

1 SIMPLE t4 const id4 NULL NULL NULL 1

2325

1 SIMPLE t1 ALL NULL NULL NULL NULL 2

2326

1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where

2327

2187

select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3

2328

2188

left join t4 on id3 = id4 where id2 = 1 or id4 = 1;

2329

2189

id1 id2 id3 id4 id44

2353

2213

create table t2 (c integer, d integer, index(c), index(d));

2354

2214

insert into t1 values (1,2), (2,2), (3,2), (4,2);

2355

2215

insert into t2 values (1,3), (2,3), (3,4), (4,4);

2356

explain select * from t1 left join t2 on a=c where d in (4);

2357

id select_type table type possible_keys key key_len ref rows Extra

2358

1 SIMPLE t2 ref c,d d 5 const 2 Using index condition

2359

1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer

2360

2216

select * from t1 left join t2 on a=c where d in (4);

2361

2217

a b c d

2362

2218

3 2 3 4

2363

2219

4 2 4 4

2364

explain select * from t1 left join t2 on a=c where d = 4;

2365

id select_type table type possible_keys key key_len ref rows Extra

2366

1 SIMPLE t2 ref c,d d 5 const 2 Using index condition

2367

1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer

2368

2220

select * from t1 left join t2 on a=c where d = 4;

2369

2221

a b c d

2370

2222

3 2 3 4

2390

2242

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a;

2391

2243

id select_type table type possible_keys key key_len ref rows Extra

2392

2244

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

2393

1 SIMPLE t2 ref a a 23 test.t1.a 2

2245

1 SIMPLE t2 ref a a 23 test.t1.a 1

2394

2246

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a;

2395

2247

id select_type table type possible_keys key key_len ref rows Extra

2396

2248

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

2397

1 SIMPLE t2 ref a a 23 test.t1.a 2

2249

1 SIMPLE t2 ref a a 23 test.t1.a 1

2398

2250

DROP TABLE t1, t2;

2399

2251

CREATE TABLE t1 ( city char(30) );

2400

2252

INSERT INTO t1 VALUES ('London');

2569

2421

create table t2 (a tinyint unsigned);

2570

2422

create index t2i on t2(a);

2571

2423

insert into t2 values (0), (254), (255);

2572

explain select * from t2 where a > -1;

2573

id select_type table type possible_keys key key_len ref rows Extra

2574

1 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index

2575

2424

select * from t2 where a > -1;

2576

2425

a

2577

2426

0

2609

2458

insert into t1 values(1,""),(2,"");

2610

2459

show table status like 't1%';

2611

2460

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

2612

t1 MyISAM 10 Dynamic 2 20 X X X X X X X X latin1_swedish_ci NULL

2613

t11 MyISAM 10 Dynamic 0 0 X X X X X X X X latin1_swedish_ci NULL

2461

t1 InnoDB 10 Compact 2 8192 X X X X X X X X latin1_swedish_ci NULL

2462

t11 InnoDB 10 Compact 0 0 X X X X X X X X latin1_swedish_ci NULL

2614

2463

select 123 as a from t1 where f1 is null;

2615

2464

a

2616

2465

drop table t1,t11;

2670

2519

insert into t2 values (1,'',''), (2,'','');

2671

2520

CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b));

2672

2521

insert into t3 values (1,1),(1,2);

2673

explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2

2674

where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and

2675

t2.b like '%%' order by t2.b limit 0,1;

2676

id select_type table type possible_keys key key_len ref rows Extra

2677

1 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort

2678

1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer

2679

1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)

2680

2522

DROP TABLE t1,t2,t3;

2681

2523

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

2682

2524

INSERT INTO t1 VALUES (2), (3), (1);

2693

2535

CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b));

2694

2536

INSERT INTO t2 VALUES (1,NULL), (2,10);

2695

2537

ALTER TABLE t1 ENABLE KEYS;

2538

Warnings:

2539

Note 1031 Table storage engine for 't1' doesn't have this option

2696

2540

EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;

2697

2541

id select_type table type possible_keys key key_len ref rows Extra

2698

1 SIMPLE t2 index b b 5 NULL 2 Using index

2542

1 SIMPLE t2 index b PRIMARY 4 NULL 2

2699

2543

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer

2700

2544

SELECT STRAIGHT_JOIN * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;

2701

2545

a b a b

2705

2549

2 10 4 10

2706

2550

EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;

2707

2551

id select_type table type possible_keys key key_len ref rows Extra

2708

1 SIMPLE t2 index b b 5 NULL 2 Using index

2552

1 SIMPLE t2 index b PRIMARY 4 NULL 2

2709

2553

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer

2710

2554

SELECT STRAIGHT_JOIN * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;

2711

2555

a b a b

2718

2562

CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2));

2719

2563

INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941);

2720

2564

INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941);

2721

explain select max(key1) from t1 where key1 <= 0.6158;

2722

id select_type table type possible_keys key key_len ref rows Extra

2723

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2724

explain select max(key2) from t2 where key2 <= 1.6158;

2725

id select_type table type possible_keys key key_len ref rows Extra

2726

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2727

explain select min(key1) from t1 where key1 >= 0.3762;

2728

id select_type table type possible_keys key key_len ref rows Extra

2729

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2730

explain select min(key2) from t2 where key2 >= 1.3762;

2731

id select_type table type possible_keys key key_len ref rows Extra

2732

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2733

explain select max(key1), min(key2) from t1, t2

2734

where key1 <= 0.6158 and key2 >= 1.3762;

2735

id select_type table type possible_keys key key_len ref rows Extra

2736

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2737

explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;

2738

id select_type table type possible_keys key key_len ref rows Extra

2739

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2740

explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;

2741

id select_type table type possible_keys key key_len ref rows Extra

2742

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2743

2565

select max(key1) from t1 where key1 <= 0.6158;

2744

2566

max(key1)

2745

2567

0.6158

3129

2951

FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);

3130

2952

id select_type table type possible_keys key key_len ref rows Extra

3131

2953

1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1

3132

1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR

2954

1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where

3133

2955

DROP TABLE t1,t2;

3134

2956

CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);

3135

2957

INSERT t1 SET i = 0;

3162

2984

select 'In next EXPLAIN, B.rows must be exactly 10:' Z;

3163

2985

Z

3164

2986

In next EXPLAIN, B.rows must be exactly 10:

3165

explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5

3166

and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);

3167

id select_type table type possible_keys key key_len ref rows Extra

3168

1 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using index condition; Using where; Using MRR

3169

1 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 10

3170

2987

drop table t1, t2;

3171

2988

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

3172

2989

INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2),

3179

2996

EXPLAIN

3180

2997

SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;

3181

2998

id select_type table type possible_keys key key_len ref rows Extra

3182

1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using MRR

3183

1 SIMPLE t2 ref c c 5 test.t1.a 2

2999

1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where; Using index

3000

1 SIMPLE t2 ref c c 5 test.t1.a 1

3184

3001

EXPLAIN

3185

3002

SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;

3186

3003

id select_type table type possible_keys key key_len ref rows Extra

3187

1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where; Using MRR

3188

1 SIMPLE t2 ref c c 5 test.t1.a 2

3004

1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where; Using index

3005

1 SIMPLE t2 ref c c 5 test.t1.a 1

3189

3006

DROP TABLE t1, t2;

3190

3007

create table t1 (

3191

3008

a int unsigned not null auto_increment primary key,

3239

3056

id select_type table type possible_keys key key_len ref rows Extra

3240

3057

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3241

3058

1 SIMPLE t2 const idx1 NULL NULL NULL 1

3242

1 SIMPLE t3 ref idx1 idx1 5 const 3

3059

1 SIMPLE t3 ref idx1 idx1 5 const 2

3243

3060

SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id

3244

3061

WHERE t1.id=2;

3245

3062

id a b c d e

3262

3079

FROM t1 JOIN t2 ON t2.fk=t1.pk

3263

3080

WHERE t2.fk < 'c' AND t2.pk=t1.fk;

3264

3081

id select_type table type possible_keys key key_len ref rows Extra

3265

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using MRR

3082

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where

3266

3083

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where

3267

3084

EXPLAIN SELECT t2.*

3268

3085

FROM t1 JOIN t2 ON t2.fk=t1.pk

3269

3086

WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;

3270

3087

id select_type table type possible_keys key key_len ref rows Extra

3271

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR

3088

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where

3272

3089

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where

3273

3090

EXPLAIN SELECT t2.*

3274

3091

FROM t1 JOIN t2 ON t2.fk=t1.pk

3275

3092

WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;

3276

3093

id select_type table type possible_keys key key_len ref rows Extra

3277

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR

3094

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where

3278

3095

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where

3279

3096

DROP TABLE t1,t2;

3280

3097

CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));

3317

3134

t3.a=t2.a AND t3.c IN ('bb','ee') ;

3318

3135

id select_type table type possible_keys key key_len ref rows Extra

3319

3136

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3320

1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using MRR

3321

1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where

3137

1 SIMPLE t3 range PRIMARY,ci ci 5 NULL 6 Using where; Using index

3138

1 SIMPLE t2 ref si,ai ai 5 test.t3.a 1 Using where

3322

3139

EXPLAIN

3323

3140

SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3

3324

3141

WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND

3370

3187

2

3371

3188

EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;

3372

3189

id select_type table type possible_keys key key_len ref rows Extra

3373

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3190

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,5 NULL 1 Using intersect(idx2,idx1); Using where; Using index

3374

3191

DROP INDEX idx1 ON t1;

3375

3192

CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);

3376

3193

EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;

3377

3194

id select_type table type possible_keys key key_len ref rows Extra

3378

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3195

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,5 NULL 1 Using intersect(idx2,idx1); Using where; Using index

3379

3196

DROP TABLE t1;

3380

3197

CREATE TABLE t1 (

3381

3198

ID1_with_null int NULL,

3407

3224

EXPLAIN SELECT * FROM t1

3408

3225

WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;

3409

3226

id select_type table type possible_keys key key_len ref rows Extra

3410

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3227

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,10 NULL 1 Using intersect(idx2,idx1); Using where; Using index

3411

3228

EXPLAIN SELECT * FROM t1

3412

3229

WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ;

3413

3230

id select_type table type possible_keys key key_len ref rows Extra

3414

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3231

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where

3415

3232

EXPLAIN SELECT * FROM t1

3416

3233

WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;

3417

3234

id select_type table type possible_keys key key_len ref rows Extra

3418

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3235

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where

3419

3236

DROP INDEX idx1 ON t1;

3420

3237

CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null);

3421

3238

EXPLAIN SELECT * FROM t1

3422

3239

WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;

3423

3240

id select_type table type possible_keys key key_len ref rows Extra

3424

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3241

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,10 NULL 1 Using intersect(idx2,idx1); Using where; Using index

3425

3242

EXPLAIN SELECT * FROM t1

3426

3243

WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ;

3427

3244

id select_type table type possible_keys key key_len ref rows Extra

3428

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3245

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,10 NULL 1 Using intersect(idx2,idx1); Using where; Using index

3429

3246

EXPLAIN SELECT * FROM t1

3430

3247

WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;

3431

3248

id select_type table type possible_keys key key_len ref rows Extra

3432

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3249

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where

3433

3250

EXPLAIN SELECT * FROM t1

3434

3251

WHERE ID_better=1 AND ID1_with_null IS NULL AND

3435

3252

(ID2_with_null=1 OR ID2_with_null=2);

3436

3253

id select_type table type possible_keys key key_len ref rows Extra

3437

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3254

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where

3438

3255

DROP TABLE t1;

3439

3256

CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));

3440

3257

INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");

3485

3302

"i" decimal(19,0) NOT NULL,

3486

3303

"c" decimal(19,0) NOT NULL,

3487

3304

"co" decimal(19,0) NOT NULL

3488

) ENGINE=MyISAM DEFAULT CHARSET=latin1

3305

) ENGINE=InnoDB DEFAULT CHARSET=latin1

3489

3306

drop table t1;

3490

3307

select

3491

3308

if(1, cast(1111111111111111111 as unsigned), 1) i,

3528

3345

cc 3 7

3529

3346

EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';

3530

3347

id select_type table type possible_keys key key_len ref rows Extra

3531

1 SIMPLE t2 ref name name 6 const 3 Using where

3348

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where

3532

3349

SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';

3533

3350

name LENGTH(name) n

3534

3351

cc 5 3

3536

3353

cc 3 7

3537

3354

EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';

3538

3355

id select_type table type possible_keys key key_len ref rows Extra

3539

1 SIMPLE t2 range name name 6 NULL 3 Using where

3356

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where

3540

3357

SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';

3541

3358

name LENGTH(name) n

3542

3359

cc 5 3

3545

3362

cc 3 7

3546

3363

EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;

3547

3364

id select_type table type possible_keys key key_len ref rows Extra

3548

1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort

3365

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where; Using filesort

3549

3366

SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;

3550

3367

name LENGTH(name) n

3551

3368

cc 4 4

3555

3372

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3556

3373

id select_type table type possible_keys key key_len ref rows Extra

3557

3374

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

3558

1 SIMPLE t2 ref name name 6 test.t1.name 2

3375

1 SIMPLE t2 ref name name 6 test.t1.name 1

3559

3376

SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3560

3377

name name n

3561

3378

ccc NULL NULL

3602

3419

cc 3 7

3603

3420

EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';

3604

3421

id select_type table type possible_keys key key_len ref rows Extra

3605

1 SIMPLE t2 ref name name 6 const 3 Using where

3422

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where

3606

3423

SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';

3607

3424

name LENGTH(name) n

3608

3425

cc 5 3

3610

3427

cc 3 7

3611

3428

EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';

3612

3429

id select_type table type possible_keys key key_len ref rows Extra

3613

1 SIMPLE t2 range name name 6 NULL 3 Using where

3430

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where

3614

3431

SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';

3615

3432

name LENGTH(name) n

3616

3433

cc 5 3

3619

3436

cc 3 7

3620

3437

EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;

3621

3438

id select_type table type possible_keys key key_len ref rows Extra

3622

1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort

3439

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where; Using filesort

3623

3440

SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;

3624

3441

name LENGTH(name) n

3625

3442

cc 4 4

3629

3446

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3630

3447

id select_type table type possible_keys key key_len ref rows Extra

3631

3448

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

3632

1 SIMPLE t2 ref name name 6 test.t1.name 2

3449

1 SIMPLE t2 ref name name 6 test.t1.name 1

3633

3450

SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3634

3451

name name n

3635

3452

ccc NULL NULL

3686

3503

WHERE 1 AND f1.b NOT IN (100,2232,3343,51111);

3687

3504

id select_type table type possible_keys key key_len ref rows Extra

3688

3505

1 SIMPLE f1 index inx inx 10 NULL 7 Using where; Using index

3689

1 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where; Using index

3506

1 SIMPLE f2 ref inx inx 5 test.f1.b 3 Using where; Using index

3690

3507

DROP TABLE t1;

3691

3508

CREATE TABLE t1 (c1 INT, c2 INT);

3692

3509

INSERT INTO t1 VALUES (1,11), (2,22), (2,22);

3748

3565

join_1.c1,

3749

3566

join_0.c1;

3750

3567

id select_type table type possible_keys key key_len ref rows filtered Extra

3751

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables

3568

1 SIMPLE join_0 ALL PRIMARY NULL NULL NULL 1 100.00 Using temporary; Using filesort

3569

1 SIMPLE join_1 eq_ref PRIMARY PRIMARY 4 test.join_0.c1 1 100.00

3570

1 SIMPLE join_2 eq_ref PRIMARY PRIMARY 4 test.join_1.c1 1 100.00 Using where

3571

1 SIMPLE join_3 eq_ref PRIMARY PRIMARY 4 test.join_2.c1 1 100.00 Using where

3572

1 SIMPLE join_4 eq_ref PRIMARY PRIMARY 4 test.join_3.c1 1 100.00 Using where

3573

1 SIMPLE join_5 eq_ref PRIMARY PRIMARY 4 test.join_4.c1 1 100.00 Using where

3574

1 SIMPLE join_6 eq_ref PRIMARY PRIMARY 4 test.join_5.c1 1 100.00 Using where

3575

1 SIMPLE join_7 eq_ref PRIMARY PRIMARY 4 test.join_5.c1 1 100.00 Using where

3752

3576

Warnings:

3753

Note 1003 select '0' AS "c1" from "test"."t1" "join_0" join "test"."t1" "join_1" join "test"."t1" "join_2" join "test"."t1" "join_3" join "test"."t1" "join_4" join "test"."t1" "join_5" join "test"."t1" "join_6" join "test"."t1" "join_7" where 0 group by '0','0','0','0','0'

3577

Note 1003 select "test"."join_2"."c1" AS "c1" from "test"."t1" "join_0" join "test"."t1" "join_1" join "test"."t1" "join_2" join "test"."t1" "join_3" join "test"."t1" "join_4" join "test"."t1" "join_5" join "test"."t1" "join_6" join "test"."t1" "join_7" where ((("test"."join_1"."c1" = "test"."join_0"."c1") and ("test"."join_2"."c1" = "test"."join_0"."c1") and ("test"."join_3"."c1" = "test"."join_0"."c1") and ("test"."join_4"."c1" = "test"."join_0"."c1") and ("test"."join_5"."c1" = "test"."join_0"."c1") and ("test"."join_6"."c1" = "test"."join_0"."c1") and ("test"."join_7"."c1" = "test"."join_0"."c1")) or (("test"."join_1"."c1" = "test"."join_0"."c1") and ("test"."join_2"."c1" = "test"."join_0"."c1") and ("test"."join_3"."c1" = "test"."join_0"."c1") and ("test"."join_4"."c1" = "test"."join_0"."c1") and ("test"."join_5"."c1" = "test"."join_0"."c1") and ("test"."join_6"."c1" = "test"."join_0"."c1") and ("test"."join_7"."c1" = "test"."join_0"."c1") and ("test"."join_4"."c2" = '?') and ("test"."join_0"."c2" < '?') and ("test"."join_1"."c2" < '?') and ("test"."join_2"."c2" > '?') and ("test"."join_2"."c2" < '!') and ("test"."join_3"."c2" > '?') and ("test"."join_5"."c2" <> '?') and ("test"."join_6"."c2" <> '?') and ("test"."join_7"."c2" >= '?'))) group by "test"."join_3"."c1","test"."join_2"."c1","test"."join_1"."c1","test"."join_0"."c1"

3754

3578

SHOW WARNINGS;

3755

3579

Level Code Message

3756

Note 1003 select '0' AS "c1" from "test"."t1" "join_0" join "test"."t1" "join_1" join "test"."t1" "join_2" join "test"."t1" "join_3" join "test"."t1" "join_4" join "test"."t1" "join_5" join "test"."t1" "join_6" join "test"."t1" "join_7" where 0 group by '0','0','0','0','0'

3580

Note 1003 select "test"."join_2"."c1" AS "c1" from "test"."t1" "join_0" join "test"."t1" "join_1" join "test"."t1" "join_2" join "test"."t1" "join_3" join "test"."t1" "join_4" join "test"."t1" "join_5" join "test"."t1" "join_6" join "test"."t1" "join_7" where ((("test"."join_1"."c1" = "test"."join_0"."c1") and ("test"."join_2"."c1" = "test"."join_0"."c1") and ("test"."join_3"."c1" = "test"."join_0"."c1") and ("test"."join_4"."c1" = "test"."join_0"."c1") and ("test"."join_5"."c1" = "test"."join_0"."c1") and ("test"."join_6"."c1" = "test"."join_0"."c1") and ("test"."join_7"."c1" = "test"."join_0"."c1")) or (("test"."join_1"."c1" = "test"."join_0"."c1") and ("test"."join_2"."c1" = "test"."join_0"."c1") and ("test"."join_3"."c1" = "test"."join_0"."c1") and ("test"."join_4"."c1" = "test"."join_0"."c1") and ("test"."join_5"."c1" = "test"."join_0"."c1") and ("test"."join_6"."c1" = "test"."join_0"."c1") and ("test"."join_7"."c1" = "test"."join_0"."c1") and ("test"."join_4"."c2" = '?') and ("test"."join_0"."c2" < '?') and ("test"."join_1"."c2" < '?') and ("test"."join_2"."c2" > '?') and ("test"."join_2"."c2" < '!') and ("test"."join_3"."c2" > '?') and ("test"."join_5"."c2" <> '?') and ("test"."join_6"."c2" <> '?') and ("test"."join_7"."c2" >= '?'))) group by "test"."join_3"."c1","test"."join_2"."c1","test"."join_1"."c1","test"."join_0"."c1"

3757

3581

DROP TABLE t1;

3758

3582

SELECT 1 AS ` `;

3759

3583

3991

3815

2

3992

3816

SHOW STATUS LIKE 'Handler_read%';

3993

3817

Variable_name Value

3994

Handler_read_first 0

3995

Handler_read_key 2

3818

Handler_read_first 1

3819

Handler_read_key 5

3996

3820

Handler_read_next 0

3997

3821

Handler_read_prev 0

3998

3822

Handler_read_rnd 0