~drizzle-trunk/drizzle/development

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;

1204

1216

fld3 period price price2

1205

1217

admonishing 1002 28357832 8723648

1267

1279

18601 vacuuming 1001 5987435 234724

1268

1280

18801 inch 1001 5987435 234724

1269

1281

18811 repetitions 1001 5987435 234724

1270

create temporary table t4 (

1282

create table t4 (

1271

1283

companynr int NOT NULL default '00',

1272

1284

companyname char(30) NOT NULL default '',

1273

1285

PRIMARY KEY (companynr),

1274

1286

UNIQUE KEY companyname(companyname)

1275

) ENGINE=MyISAM COMMENT='companynames';

1287

) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';

1276

1288

select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;

1277

1289

companynr companyname

1278

1290

0 Unknown

1340

1352

1199

1341

1353

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

1342

1354

id select_type table type possible_keys key key_len ref rows Extra

1343

1 SIMPLE t2 ALL NULL NULL NULL NULL #

1344

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

1355

1 SIMPLE t2 ALL NULL NULL NULL NULL 1200

1356

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

1345

1357

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

1346

1358

id select_type table type possible_keys key key_len ref rows Extra

1347

1 SIMPLE t4 ALL NULL NULL NULL NULL #

1348

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

1359

1 SIMPLE t4 ALL NULL NULL NULL NULL 12

1360

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

1349

1361

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

1350

1362

companynr companyname

1351

1363

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

1353

1365

1200

1354

1366

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

1355

1367

id select_type table type possible_keys key key_len ref rows Extra

1356

1 SIMPLE NULL NULL NULL NULL NULL NULL # Impossible WHERE

1368

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1357

1369

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

1358

1370

id select_type table type possible_keys key key_len ref rows Extra

1359

1 SIMPLE NULL NULL NULL NULL NULL NULL # Impossible WHERE

1371

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1360

1372

delete from t2 where fld1=999999;

1361

1373

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

1362

1374

id select_type table type possible_keys key key_len ref rows Extra

1363

1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where

1364

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

1375

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1376

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

1365

1377

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

1366

1378

id select_type table type possible_keys key key_len ref rows Extra

1367

1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where

1368

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

1379

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1380

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

1369

1381

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

1370

1382

id select_type table type possible_keys key key_len ref rows Extra

1371

1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where

1372

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

1383

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1384

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

1373

1385

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

1374

1386

id select_type table type possible_keys key key_len ref rows Extra

1375

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL # Using where

1376

1 SIMPLE t2 ALL NULL NULL NULL NULL #

1387

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1388

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1377

1389

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

1378

1390

id select_type table type possible_keys key key_len ref rows Extra

1379

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL # Using where

1380

1 SIMPLE t2 ALL NULL NULL NULL NULL #

1391

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1392

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1381

1393

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

1382

1394

id select_type table type possible_keys key key_len ref rows Extra

1383

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL # Using where

1384

1 SIMPLE t2 ALL NULL NULL NULL NULL #

1395

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1396

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1385

1397

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

1386

1398

id select_type table type possible_keys key key_len ref rows Extra

1387

1 SIMPLE t4 ALL NULL NULL NULL NULL #

1388

1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where

1399

1 SIMPLE t4 ALL NULL NULL NULL NULL 12

1400

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1389

1401

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

1390

1402

id select_type table type possible_keys key key_len ref rows Extra

1391

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL #

1392

1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where

1403

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12

1404

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1393

1405

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

1394

1406

id select_type table type possible_keys key key_len ref rows Extra

1395

1 SIMPLE t4 ALL NULL NULL NULL NULL #

1396

1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where

1407

1 SIMPLE t4 ALL NULL NULL NULL NULL 12

1408

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1397

1409

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

1398

1410

id select_type table type possible_keys key key_len ref rows Extra

1399

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL # Using where

1400

1 SIMPLE t2 ALL NULL NULL NULL NULL #

1411

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1412

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1401

1413

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

1402

1414

id select_type table type possible_keys key key_len ref rows Extra

1403

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL # Using where

1404

1 SIMPLE t2 ALL NULL NULL NULL NULL #

1415

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1416

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1405

1417

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

1406

1418

id select_type table type possible_keys key key_len ref rows Extra

1407

1 SIMPLE t4 ALL NULL NULL NULL NULL # Using where

1408

1 SIMPLE t2 ALL NULL NULL NULL NULL #

1419

1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where

1420

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1409

1421

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

1410

1422

companynr companynr

1411

1423

37 36

1412

1424

41 40

1413

1425

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

1414

1426

id select_type table type possible_keys key key_len ref rows Extra

1415

1 SIMPLE t4 index NULL PRIMARY 4 NULL # Using index; Using temporary

1416

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

1427

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

1428

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

1417

1429

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;

1418

1430

fld1 companynr fld3 period

1419

1431

38008 37 reporters 1008

1487

1499

70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069

1488

1500

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

1489

1501

id select_type table type possible_keys key key_len ref rows filtered Extra

1490

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

1502

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

1491

1503

Warnings:

1492

1504

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

1493

1505

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

1928

1940

38008 234298

1929

1941

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

1930

1942

id select_type table type possible_keys key key_len ref rows Extra

1931

1 SIMPLE NULL NULL NULL NULL NULL NULL # Impossible WHERE

1943

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1932

1944

explain select fld3 from t2 where fld1=fld1;

1933

1945

id select_type table type possible_keys key key_len ref rows Extra

1934

1 SIMPLE t2 ALL NULL NULL NULL NULL #

1946

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1935

1947

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

1936

1948

companynr fld1

1937

1949

34 250501

1983

1995

4181

1984

1996

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

1985

1997

id select_type table type possible_keys key key_len ref rows Extra

1986

1 SIMPLE t2 index NULL PRIMARY 4 NULL #

1998

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

1987

1999

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

1988

2000

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

1989

2001

0 1232609 1199

2044

2056

2045

2057

2046

2058

2059

2047

2060

show tables from test like "s%";

2048

2061

Tables_in_test (s%)

2049

2062

show tables from test like "t?";

2068

2081

Field Type Collation Null Key Default Extra Privileges Comment

2069

2082

show keys from t2;

2070

2083

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

2071

t2 0 PRIMARY 1 auto A # NULL NULL BTREE

2072

t2 0 fld1 1 fld1 A # NULL NULL BTREE

2073

t2 1 fld3 1 fld3 A # NULL NULL BTREE

2084

t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE

2085

t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE

2086

t2 1 fld3 1 fld3 A NULL NULL NULL BTREE

2074

2087

drop table t4, t3, t2, t1;

2075

CREATE TEMPORARY TABLE t1 (

2088

CREATE TABLE t1 (

2076

2089

cont_nr int NOT NULL auto_increment,

2077

2090

ver_nr int NOT NULL default '0',

2078

2091

aufnr int NOT NULL default '0',

Older »