~drizzle-trunk/drizzle/development : revision 201

1282

# Test using INDEX and IGNORE INDEX

1283

#

1284

1285

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

1286

1287

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

1288

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

1289

1290

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

1291

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

1285

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

1286

1287

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

1288

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

1289

1290

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

1291

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

1292

1293

#

1294

# NOTE NOTE NOTE

1295

# The next should give an error

1296

#

1297

1298

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

1299

explain select fld3 from t2 use index (not_used);

1298

#-- error 1176

1299

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

1300

#-- error 1176

1301

#explain select fld3 from t2 use index (not_used);

1300

1302

1301

1303

#

1302

1304

# Test sorting with a used key (there is no need for sorting)

1303

1305

#

1304

1306

1305

1307

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

1306

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

1308

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

1307

1309

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

1308

1310

1309

1311

#

1323

#

1324

1325

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

1326

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

1326

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

1327

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

1328

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

1328

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

1329

1330

#

1331

# Search with a key with LIKE constant

1443

# Here the last fld3 is optimized away from the order by

1444

#

1445

1446

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

1446

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

1447

1448

#

1449

# Some test with ORDER BY and limit

1450

#

1451

1452

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

1453

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

1454

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

1452

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

1453

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

1454

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

1455

1456

#

1457

# Search with a constant table.

1468

1469

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

1470

1471

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

1471

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

1472

1473

#

1474

# Search with a constant table and several rows from another table

1539

1540

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

1541

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

1542

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

1543

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

1542

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

1543

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

1544

1545

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

1546

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

1547

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

1548

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

1547

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

1548

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

1549

delete from t2 where fld1=999999;

1550

1551

#

1552

# Test left join optimization

1553

1554

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

1555

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

1556

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

1557

1558

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

1559

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

1560

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

1561

# Following can't be optimized

1562

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

1563

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

1564

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

1565

1566

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

1567

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

1568

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

1554

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

1555

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

1556

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

1557

#

1558

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

1559

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

1560

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

1561

## Following can't be optimized

1562

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

1563

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

1564

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

1565

#

1566

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

1567

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

1568

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

1569

1570

#

1571

# Joins with forms.

1572

#

1573

1574

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

1575

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

1575

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

1576

1577

#

1578

# Search using 'or' with the same referens group.

1611

select companynr,count(*),sum(fld1) from t2 group by companynr;

1612

select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;

1613

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

1614

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

1614

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

1615

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

1616

select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;

1617

select /*! SQL_SMALL_RESULT */ companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;

1700

1701

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;

1702

1703

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

1704

explain select fld3 from t2 where fld1=fld1;

1703

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

1704

#explain select fld3 from t2 where fld1=fld1;

1705

1706

#

1707

# HAVING

1726

select max(fld1) from t2 where fld1>= 098024;

1727

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

1728

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

1729

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

1729

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

1730

# PS does correct pre-zero here. MySQL can't do it as it returns a number.

1731

--disable_ps_protocol

1732

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

1762

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

1763

--replace_column 8 #

1764

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

1765

show keys from t2;

1765

#show keys from t2;

1766

1767

drop table t4, t3, t2, t1;

1768

1879

insert into t2 values (1);

1880

insert into t4 values (1,1);

1881

1882

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

1883

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

1882

#explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 left join t4 on id3 = id4 where id2 = 1 or id4 = 1;

1884

1883

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

1885

1884

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

1886

1885

1910

1909

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

1911

1910

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

1912

1911

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

1913

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

1912

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

1914

1913

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

1915

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

1914

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

1916

1915

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

1917

1916

drop table t1, t2;

1918

1917

2136

2135

create table t2 (a tinyint unsigned);

2137

2136

create index t2i on t2(a);

2138

2137

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

2139

explain select * from t2 where a > -1;

2138

#explain select * from t2 where a > -1;

2140

2139

select * from t2 where a > -1;

2141

2140

drop table t2;

2142

2141

2223

2222

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

2224

2223

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

2225

2224

# must have "range checked" for t2

2226

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

2227

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

2228

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

2225

#explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and t2.b like '%%' order by t2.b limit 0,1;

2229

2226

DROP TABLE t1,t2,t3;

2230

2227

2231

2228

#

2268

2265

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

2269

2266

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

2270

2267

2271

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

2272

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

2273

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

2274

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

2275

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

2276

where key1 <= 0.6158 and key2 >= 1.3762;

2277

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

2278

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

2268

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

2269

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

2270

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

2271

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

2272

#explain select max(key1), min(key2) from t1, t2 where key1 <= 0.6158 and key2 >= 1.3762;

2273

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

2274

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

2279

2275

2280

2276

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

2281

2277

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

2732

2728

analyze table t2;

2733

2729

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

2734

2730

2735

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

2736

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

2731

#explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);

2737

2732

drop table t1, t2;

2738

2733

2739

2734

#