~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/r/order_by.result

Committer: Brian Aker
Date: 2008-10-11 00:19:38 UTC
Revision ID: brian@tangent.org-20081011001938-pka194f1inm3cx1s

Removed EXPL from tests. Why? The explain output is based on MyISAM...
worthless for Innodb. Few realize this...

files modified:
tests/r/order_by.result

tests/t/order_by.test

Show diffs side-by-side

added added

removed removed

tests/r/order_by.result

471

104619 5 75

472

104505 5 117

473

103853 5 250

474

EXPLAIN select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid;

475

id select_type table type possible_keys key key_len ref rows Extra

476

1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort

477

1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.uid 1 Using index

478

1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index

479

EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;

480

id select_type table type possible_keys key key_len ref rows Extra

481

1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort

482

1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using index

483

EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;

484

id select_type table type possible_keys key key_len ref rows Extra

485

1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort

486

1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index

487

1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.uid 1 Using index

488

EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;

489

id select_type table type possible_keys key key_len ref rows Extra

490

1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort

491

1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using index

492

EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;

493

id select_type table type possible_keys key key_len ref rows Extra

494

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

495

1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.skr 1

474

select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid;

475

gid sid uid

476

103853 5 250

477

103867 5 27

478

103962 5 27

479

104505 5 117

480

104619 5 75

481

104620 5 15

482

SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;

483

gid uid

484

SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;

485

gid sid uid

486

104620 5 15

487

103867 5 27

488

103962 5 27

489

104619 5 75

490

104505 5 117

491

103853 5 250

492

SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;

493

gid uid

494

SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;

495

gid uid

496

drop table t1,t2,t3;

497

CREATE TABLE t1 (

498

`titre` char(80) NOT NULL default '',

1573

KEY StringField (FieldKey,StringVal(32))

1574

);

1575

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

1576

EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;

1577

id select_type table type possible_keys key key_len ref rows Extra

1578

1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 146 const 3 Using where

1579

1576

SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;

1580

1577

FieldKey LongVal StringVal

1581

1578

1 0 2

1582

1579

1 1 3

1583

1580

1 2 1

1584

EXPLAIN SELECT * FROM t1 ignore index (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal;

1585

id select_type table type possible_keys key key_len ref rows Extra

1586

1 SIMPLE t1 range StringField StringField 146 NULL 3 Using index condition; Using filesort

1587

1581

SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;

1588

1582

FieldKey LongVal StringVal

1589

1583

3 1 2

1590

1584

3 2 1

1591

1585

3 3 3

1592

EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;

1593

id select_type table type possible_keys key key_len ref rows Extra

1594

1 SIMPLE t1 range FieldKey,LongField,StringField LongField 146 NULL 3 Using where

1595

1586

SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;

1596

1587

FieldKey LongVal StringVal

1597

1588

3 1 2

2042

2033

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

2043

2034

CREATE TABLE t2 (a INT, b INT, KEY a (a,b));

2044

2035

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

2045

EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;

2046

id select_type table type possible_keys key key_len ref rows Extra

2047

1 SIMPLE t1 const PRIMARY,b b 5 const 1 Using index

2048

1 SIMPLE t2 ref a a 5 const 2 Using where; Using index

2036

SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;

2037

2038

2039

2049

2040

DROP TABLE t1,t2;

2050

2041

CREATE TABLE t1 (

2051

2042

a INT,

2059

2050

INSERT INTO t1 SELECT a +16, b +16 FROM t1;

2060

2051

INSERT INTO t1 SELECT a +32, b +32 FROM t1;

2061

2052

INSERT INTO t1 SELECT a +64, b +64 FROM t1;

2062

EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;

2063

id select_type table type possible_keys key key_len ref rows Extra

2064

1 SIMPLE t1 range NULL PRIMARY 4 NULL 65 Using index for group-by

2065

2053

SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;

2066

2054

2067

2055

2195

2183

SELECT @tmp_tables_after = @tmp_tables_before ;

2196

2184

@tmp_tables_after = @tmp_tables_before

2197

2185

NULL

2198

EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;

2199

id select_type table type possible_keys key key_len ref rows Extra

2200

1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using index

2201

2186

SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;

2202

2187

2203

2188

2351

2336

CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b));

2352

2337

CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b));

2353

2338

INSERT INTO t3 SELECT * FROM t1;

2354

EXPLAIN

2355

SELECT d FROM t1, t2

2356

WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'

2357

ORDER BY t2.c LIMIT 1;

2358

id select_type table type possible_keys key key_len ref rows Extra

2359

1 SIMPLE t1 ref a,b b 15 const 4 Using index condition; Using temporary; Using filesort

2360

1 SIMPLE t2 ref a,b,c a 136 test.t1.a,const 1 Using index condition

2361

2339

SELECT d FROM t1, t2

2362

2340

WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'

2363

2341

ORDER BY t2.c LIMIT 1;

2364

2342

2365

2343

52.5

2366

EXPLAIN

2367

SELECT d FROM t3 AS t1, t2 AS t2

2368

WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'

2369

ORDER BY t2.c LIMIT 1;

2370

id select_type table type possible_keys key key_len ref rows Extra

2371

1 SIMPLE t2 range a,b,c c 9 NULL 127 Using where; Using MRR

2372

1 SIMPLE t1 ref a a 146 test.t2.a,const 1 Using where; Using index

2373

2344

SELECT d FROM t3 AS t1, t2 AS t2

2374

2345

WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'

2375

2346

ORDER BY t2.c LIMIT 1;

Older »