~drizzle-trunk/drizzle/development : revision 1

1

#---------------- Index merge test 2 -------------------------------------------

2

SET SESSION STORAGE_ENGINE = InnoDB;

3

drop table if exists t1,t2;

4

create table t1

5

(

6

key1 int not null,

7

key2 int not null,

8

INDEX i1(key1),

9

INDEX i2(key2)

10

);

11

explain select * from t1 where key1 < 5 or key2 > 197;

12

id select_type table type possible_keys key key_len ref rows Extra

13

1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where

14

select * from t1 where key1 < 5 or key2 > 197;

15

key1 key2

16

0 200

17

1 199

18

2 198

19

3 197

20

4 196

21

explain select * from t1 where key1 < 3 or key2 > 195;

22

id select_type table type possible_keys key key_len ref rows Extra

23

1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where

24

select * from t1 where key1 < 3 or key2 > 195;

25

key1 key2

26

0 200

27

1 199

28

2 198

29

3 197

30

4 196

31

alter table t1 add str1 char (255) not null,

32

add zeroval int not null default 0,

33

add str2 char (255) not null,

34

add str3 char (255) not null;

35

update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));

36

alter table t1 add primary key (str1, zeroval, str2, str3);

37

explain select * from t1 where key1 < 5 or key2 > 197;

38

id select_type table type possible_keys key key_len ref rows Extra

39

1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where

40

select * from t1 where key1 < 5 or key2 > 197;

41

key1 key2 str1 zeroval str2 str3

42

4 196 aaa 0 bbb 196-2_a

43

3 197 aaa 0 bbb 197-1_A

44

2 198 aaa 0 bbb 198-1_a

45

1 199 aaa 0 bbb 199-0_A

46

0 200 aaa 0 bbb 200-0_a

47

explain select * from t1 where key1 < 3 or key2 > 195;

48

id select_type table type possible_keys key key_len ref rows Extra

49

1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where

50

select * from t1 where key1 < 3 or key2 > 195;

51

key1 key2 str1 zeroval str2 str3

52

4 196 aaa 0 bbb 196-2_a

53

3 197 aaa 0 bbb 197-1_A

54

2 198 aaa 0 bbb 198-1_a

55

1 199 aaa 0 bbb 199-0_A

56

0 200 aaa 0 bbb 200-0_a

57

drop table t1;

58

create table t1 (

59

pk integer not null auto_increment primary key,

60

key1 integer,

61

key2 integer not null,

62

filler char (200),

63

index (key1),

64

index (key2)

65

);

66

show warnings;

67

Level Code Message

68

explain select pk from t1 where key1 = 1 and key2 = 1;

69

id select_type table type possible_keys key key_len ref rows Extra

70

1 SIMPLE t1 index_merge key1,key2 key1,key2 5,4 NULL 1 Using intersect(key1,key2); Using where; Using index

71

select pk from t1 where key2 = 1 and key1 = 1;

72

pk

73

26

74

27

75

select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;

76

pk

77

26

78

27

79

drop table t1;

80

create table t1 (

81

pk int primary key auto_increment,

82

key1a int,

83

key2a int,

84

key1b int,

85

key2b int,

86

dummy1 int,

87

dummy2 int,

88

dummy3 int,

89

dummy4 int,

90

key3a int,

91

key3b int,

92

filler1 char (200),

93

index i1(key1a, key1b),

94

index i2(key2a, key2b),

95

index i3(key3a, key3b)

96

);

97

create table t2 (a int);

98

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

99

insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)

100

select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;

101

insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)

102

select key1a, key1b, key2a, key2b, key3a, key3b from t1;

103

insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)

104

select key1a, key1b, key2a, key2b, key3a, key3b from t1;

105

analyze table t1;

106

Table Op Msg_type Msg_text

107

test.t1 analyze status OK

108

select count(*) from t1;

109

count(*)

110

5184

111

explain select count(*) from t1 where

112

key1a = 2 and key1b is null and key2a = 2 and key2b is null;

113

id select_type table type possible_keys key key_len ref rows Extra

114

1 SIMPLE t1 index_merge i1,i2 i1,i2 10,10 NULL 4 Using intersect(i1,i2); Using where; Using index

115

select count(*) from t1 where

116

key1a = 2 and key1b is null and key2a = 2 and key2b is null;

117

count(*)

118

4

119

explain select count(*) from t1 where

120

key1a = 2 and key1b is null and key3a = 2 and key3b is null;

121

id select_type table type possible_keys key key_len ref rows Extra

122

1 SIMPLE t1 index_merge i1,i3 i1,i3 10,10 NULL 4 Using intersect(i1,i3); Using where; Using index

123

select count(*) from t1 where

124

key1a = 2 and key1b is null and key3a = 2 and key3b is null;

125

count(*)

126

4

127

drop table t1,t2;

128

create table t1 (

129

id1 int,

130

id2 date ,

131

index idx2 (id1,id2),

132

index idx1 (id2)

133

);

134

insert into t1 values(1,'20040101'), (2,'20040102');

135

select * from t1 where id1 = 1 and id2= '20040101';

136

id1 id2

137

1 2004-01-01

138

drop table t1;

139

create table t1

140

(

141

key1 int not null,

142

key2 int not null default 0,

143

key3 int not null default 0

144

);

145

insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);

146

set @d=8;

147

insert into t1 (key1) select key1+@d from t1;

148

set @d=@d*2;

149

insert into t1 (key1) select key1+@d from t1;

150

set @d=@d*2;

151

insert into t1 (key1) select key1+@d from t1;

152

set @d=@d*2;

153

insert into t1 (key1) select key1+@d from t1;

154

set @d=@d*2;

155

insert into t1 (key1) select key1+@d from t1;

156

set @d=@d*2;

157

insert into t1 (key1) select key1+@d from t1;

158

set @d=@d*2;

159

insert into t1 (key1) select key1+@d from t1;

160

set @d=@d*2;

161

alter table t1 add index i2(key2);

162

alter table t1 add index i3(key3);

163

update t1 set key2=key1,key3=key1;

164

explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);

165

id select_type table type possible_keys key key_len ref rows Extra

166

1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 9 Using sort_union(i3,i2); Using where

167

select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);

168

key1 key2 key3

169

31 31 31

170

32 32 32

171

33 33 33

172

34 34 34

173

35 35 35

174

36 36 36

175

37 37 37

176

38 38 38

177

39 39 39

178

drop table t1;

179

#---------------- 2-sweeps read Index merge test 2 -------------------------------

180

SET SESSION STORAGE_ENGINE = InnoDB;

181

drop table if exists t1;

182

create table t1 (

183

pk int primary key,

184

key1 int,

185

key2 int,

186

filler char(200),

187

filler2 char(200),

188

index(key1),

189

index(key2)

190

);

191

select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 );

192

pk key1 key2 filler filler2

193

2 2 2 filler-data filler-data-2

194

3 3 3 filler-data filler-data-2

195

9 9 9 filler-data filler-data-2

196

10 10 10 filler-data filler-data-2

197

4 4 4 filler-data filler-data-2

198

5 5 5 filler-data filler-data-2

199

6 6 6 filler-data filler-data-2

200

7 7 7 filler-data filler-data-2

201

8 8 8 filler-data filler-data-2

202

set @maxv=1000;

203

select * from t1 where

204

(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)

205

or key1=18 or key1=60;

206

pk key1 key2 filler filler2

207

18 18 18 filler-data filler-data-2

208

60 60 60 filler-data filler-data-2

209

1 1 1 filler-data filler-data-2

210

2 2 2 filler-data filler-data-2

211

3 3 3 filler-data filler-data-2

212

4 4 4 filler-data filler-data-2

213

11 11 11 filler-data filler-data-2

214

12 12 12 filler-data filler-data-2

215

13 13 13 filler-data filler-data-2

216

14 14 14 filler-data filler-data-2

217

50 50 50 filler-data filler-data-2

218

51 51 51 filler-data filler-data-2

219

52 52 52 filler-data filler-data-2

220

53 53 53 filler-data filler-data-2

221

54 54 54 filler-data filler-data-2

222

991 991 991 filler-data filler-data-2

223

992 992 992 filler-data filler-data-2

224

993 993 993 filler-data filler-data-2

225

994 994 994 filler-data filler-data-2

226

995 995 995 filler-data filler-data-2

227

996 996 996 filler-data filler-data-2

228

997 997 997 filler-data filler-data-2

229

998 998 998 filler-data filler-data-2

230

999 999 999 filler-data filler-data-2

231

1000 1000 1000 filler-data filler-data-2

232

select * from t1 where

233

(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)

234

or key1 < 3 or key1 > @maxv-11;

235

pk key1 key2 filler filler2

236

990 990 990 filler-data filler-data-2

237

1 1 1 filler-data filler-data-2

238

2 2 2 filler-data filler-data-2

239

3 3 3 filler-data filler-data-2

240

4 4 4 filler-data filler-data-2

241

11 11 11 filler-data filler-data-2

242

12 12 12 filler-data filler-data-2

243

13 13 13 filler-data filler-data-2

244

14 14 14 filler-data filler-data-2

245

50 50 50 filler-data filler-data-2

246

51 51 51 filler-data filler-data-2

247

52 52 52 filler-data filler-data-2

248

53 53 53 filler-data filler-data-2

249

54 54 54 filler-data filler-data-2

250

991 991 991 filler-data filler-data-2

251

992 992 992 filler-data filler-data-2

252

993 993 993 filler-data filler-data-2

253

994 994 994 filler-data filler-data-2

254

995 995 995 filler-data filler-data-2

255

996 996 996 filler-data filler-data-2

256

997 997 997 filler-data filler-data-2

257

998 998 998 filler-data filler-data-2

258

999 999 999 filler-data filler-data-2

259

1000 1000 1000 filler-data filler-data-2

260

select * from t1 where

261

(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)

262

or

263

(key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10);

264

pk key1 key2 filler filler2

265

1 1 1 filler-data filler-data-2

266

2 2 2 filler-data filler-data-2

267

3 3 3 filler-data filler-data-2

268

4 4 4 filler-data filler-data-2

269

11 11 11 filler-data filler-data-2

270

12 12 12 filler-data filler-data-2

271

13 13 13 filler-data filler-data-2

272

14 14 14 filler-data filler-data-2

273

50 50 50 filler-data filler-data-2

274

51 51 51 filler-data filler-data-2

275

52 52 52 filler-data filler-data-2

276

53 53 53 filler-data filler-data-2

277

54 54 54 filler-data filler-data-2

278

991 991 991 filler-data filler-data-2

279

992 992 992 filler-data filler-data-2

280

993 993 993 filler-data filler-data-2

281

994 994 994 filler-data filler-data-2

282

995 995 995 filler-data filler-data-2

283

996 996 996 filler-data filler-data-2

284

997 997 997 filler-data filler-data-2

285

998 998 998 filler-data filler-data-2

286

999 999 999 filler-data filler-data-2

287

1000 1000 1000 filler-data filler-data-2

288

select * from t1 where

289

(pk > 10 and pk < 15) or (pk >= 50 and pk < 55 )

290

or

291

(key1 < 5) or (key1 > @maxv-10);

292

pk key1 key2 filler filler2

293

1 1 1 filler-data filler-data-2

294

2 2 2 filler-data filler-data-2

295

3 3 3 filler-data filler-data-2

296

4 4 4 filler-data filler-data-2

297

991 991 991 filler-data filler-data-2

298

992 992 992 filler-data filler-data-2

299

993 993 993 filler-data filler-data-2

300

994 994 994 filler-data filler-data-2

301

995 995 995 filler-data filler-data-2

302

996 996 996 filler-data filler-data-2

303

997 997 997 filler-data filler-data-2

304

998 998 998 filler-data filler-data-2

305

999 999 999 filler-data filler-data-2

306

1000 1000 1000 filler-data filler-data-2

307

11 11 11 filler-data filler-data-2

308

12 12 12 filler-data filler-data-2

309

13 13 13 filler-data filler-data-2

310

14 14 14 filler-data filler-data-2

311

50 50 50 filler-data filler-data-2

312

51 51 51 filler-data filler-data-2

313

52 52 52 filler-data filler-data-2

314

53 53 53 filler-data filler-data-2

315

54 54 54 filler-data filler-data-2

316

drop table t1;

317

#---------------- Clustered PK ROR-index_merge tests -----------------------------

318

SET SESSION STORAGE_ENGINE = InnoDB;

319

drop table if exists t1;

320

create table t1

321

(

322

pk1 int not null,

323

pk2 int not null,

324

key1 int not null,

325

key2 int not null,

326

pktail1ok int not null,

327

pktail2ok int not null,

328

pktail3bad int not null,

329

pktail4bad int not null,

330

pktail5bad int not null,

331

pk2copy int not null,

332

badkey int not null,

333

filler1 char (200),

334

filler2 char (200),

335

key (key1),

336

key (key2),

337

/* keys with tails from CPK members */

338

key (pktail1ok, pk1),

339

key (pktail2ok, pk1, pk2),

340

key (pktail3bad, pk2, pk1),

341

key (pktail4bad, pk1, pk2copy),

342

key (pktail5bad, pk1, pk2, pk2copy),

343

primary key (pk1, pk2)

344

);

345

explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;

346

id select_type table type possible_keys key key_len ref rows Extra

347

1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using where

348

select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;

349

pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2

350

1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2

351

1 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2

352

1 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2

353

1 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2

354

1 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2

355

1 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2

356

1 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2

357

1 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2

358

1 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2

359

1 19 0 0 0 0 0 0 0 19 0 filler-data-19 filler2

360

explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;

361

id select_type table type possible_keys key key_len ref rows Extra

362

1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where; Using index

363

select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;

364

pk1 pk2

365

95 50

366

95 51

367

95 52

368

95 53

369

95 54

370

95 55

371

95 56

372

95 57

373

95 58

374

95 59

375

explain select * from t1 where badkey=1 and key1=10;

376

id select_type table type possible_keys key key_len ref rows Extra

377

1 SIMPLE t1 ref key1 key1 4 const 100 Using where

378

explain select * from t1 where pk1 < 7500 and key1 = 10;

379

id select_type table type possible_keys key key_len ref rows Extra

380

1 SIMPLE t1 index_merge PRIMARY,key1 key1,PRIMARY 4,4 NULL ROWS Using intersect(key1,PRIMARY); Using where

381

explain select * from t1 where pktail1ok=1 and key1=10;

382

id select_type table type possible_keys key key_len ref rows Extra

383

1 SIMPLE t1 index_merge key1,pktail1ok key1,pktail1ok 4,4 NULL 1 Using intersect(key1,pktail1ok); Using where

384

explain select * from t1 where pktail2ok=1 and key1=10;

385

id select_type table type possible_keys key key_len ref rows Extra

386

1 SIMPLE t1 index_merge key1,pktail2ok key1,pktail2ok 4,4 NULL 1 Using intersect(key1,pktail2ok); Using where

387

explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;

388

id select_type table type possible_keys key key_len ref rows Extra

389

1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 199 Using sort_union(pktail2ok,key1); Using where

390

explain select * from t1 where pktail3bad=1 and key1=10;

391

id select_type table type possible_keys key key_len ref rows Extra

392

1 SIMPLE t1 ref key1,pktail3bad key1 4 const 100 Using where

393

explain select * from t1 where pktail4bad=1 and key1=10;

394

id select_type table type possible_keys key key_len ref rows Extra

395

1 SIMPLE t1 ref key1,pktail4bad key1 4 const 100 Using where

396

explain select * from t1 where pktail5bad=1 and key1=10;

397

id select_type table type possible_keys key key_len ref rows Extra

398

1 SIMPLE t1 ref key1,pktail5bad key1 4 const 100 Using where

399

explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;

400

id select_type table type possible_keys key key_len ref rows Extra

401

1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where; Using index

402

select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;

403

pk1 pk2 key1 key2

404

95 50 10 10

405

95 51 10 10

406

95 52 10 10

407

95 53 10 10

408

95 54 10 10

409

95 55 10 10

410

95 56 10 10

411

95 57 10 10

412

95 58 10 10

413

95 59 10 10

414

drop table t1;

415

create table t1

416

(

417

RUNID varchar(22),

418

SUBMITNR varchar(5),

419

ORDERNR char(1),

420

PROGRAMM varchar(8),

421

TESTID varchar(4),

422

UCCHECK char(1),

423

ETEXT varchar(80),

424

ETEXT_TYPE char(1),

425

INFO char(1),

426

SEVERITY tinyint(3),

427

TADIRFLAG char(1),

428

PRIMARY KEY (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),

429

KEY `TVERM~KEY` (PROGRAMM,TESTID,UCCHECK)

430

) DEFAULT CHARSET=latin1;

431

update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`=''

432

WHERE

433

`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND

434

`TESTID`='' AND `UCCHECK`='';

435

drop table t1;