~drizzle-trunk/drizzle/development

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;

1283

1367

a1 a2 b min(c)

1368

b b a k221

1369

c b a k321

1370

d b a k421

1284

1371

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;

1285

1372

a1 a2 b min(c)

1373

b b a k221

1374

c b a k321

1375

d b a k421

1286

1376

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

1287

1377

a1 a2 b min(c)

1288

1378

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;

1319

1409

d b a i421 l421

1320

1410

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;

1321

1411

a1 a2 b min(c)

1412

b b a k221

1413

c b a k321

1414

d b a k421

1322

1415

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;

1323

1416

a1 a2 b min(c)

1417

b b a k221

1418

c b a k321

1419

d b a k421

1324

1420

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

1325

1421

a1 a2 b min(c)

1326

1422

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

1836

1932

1837

1933

1838

1934

drop table t1,t2,t3;

1839

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

1935

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

1840

1936

insert into t1 (c1,c2) values

1841

1937

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

1842

1938

select distinct c1, c2 from t1 order by c2;

1869

1965

drop table t1;

1870

1966

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

1871

1967

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

1872

OPTIMIZE TABLE t1;

1873

Table Op Msg_type Msg_text

1874

test.t1 optimize status OK

1968

ALTER TABLE t1 ENGINE="DEFAULT";

1875

1969

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

1876

1970

1877

1971

2169

2263

2 1 3 2.0000

2170

2264

1 1 3 2.0000

2171

2265

DROP TABLE t1;

2266

CREATE TABLE t1 (a int, b int, c int, d int,

2267

KEY foo (c,d,a,b), KEY bar (c,a,b,d));

2268

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

2269

INSERT INTO t1 SELECT * FROM t1;

2270

INSERT INTO t1 SELECT * FROM t1;

2271

INSERT INTO t1 SELECT a,b,c+1,d FROM t1;

2272

EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4;

2273

id select_type table type possible_keys key key_len ref rows Extra

2274

1 SIMPLE t1 range NULL foo 10 NULL 17 Using where; Using index for group-by

2275

SELECT DISTINCT c FROM t1 WHERE d=4;

2276

2277

2278

2279

DROP TABLE t1;

Older »