« back to all changes in this revision
Viewing changes to tests/r/pool_of_threads.result
- browse files at revision 502
- compare with another revision
- compare with revision 505
- download diff
- download tarball
- view history from revision 502
- Committer: Brian Aker
- Date: 2008-10-11 02:12:38 UTC
- Revision ID: brian@tangent.org-20081011021238-bq0uu7pzrx20zaq2
Modified test to just use myisam. This should allow us to continue to use
the EXPLAINS in it.
the EXPLAINS in it.
- files modified:
- tests/include/common-tests.inc
added
removed
tests/r/pool_of_threads.result
2
2
CREATE TABLE t1 (
3
3
Period int DEFAULT '0000' NOT NULL,
4
4
Varor_period int DEFAULT '0' NOT NULL
5
);
5
) ENGINE=myisam;
6
6
INSERT INTO t1 VALUES (9410,9412);
7
7
select period from t1;
8
8
period
24
24
UNIQUE fld1 (fld1),
25
25
KEY fld3 (fld3),
26
26
PRIMARY KEY (auto)
27
);
27
) ENGINE=myisam;
28
28
select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";
29
29
fld3
30
30
imaginable
131
131
1 SIMPLE t2 ref fld3 fld3 122 const 1 Using where; Using index
132
132
explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle';
133
133
id select_type table type possible_keys key key_len ref rows Extra
134
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275 Using where
134
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
135
135
explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle';
136
136
id select_type table type possible_keys key key_len ref rows Extra
137
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275 Using where
137
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
138
138
explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle';
139
139
id select_type table type possible_keys key key_len ref rows Extra
140
140
1 SIMPLE t2 ref fld3 fld3 122 const 1 Using where; Using index
183
183
1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index
184
184
select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';
185
185
fld1 fld3
186
11402 Romans
187
11403 intercepted
188
11703 sumac
186
189
12001 flanking
190
12004 subjective
191
12005 scatterbrain
192
12303 Kane
193
12304 overlay
194
12305 perturb
195
12306 goblins
196
12602 Wotan
197
12603 snatching
198
12605 laterally
199
12606 yelped
200
12701 grazing
201
12704 misunderstander
202
13601 handgun
187
203
13602 foldout
204
13603 mystic
205
13604 succumbed
206
13605 Nabisco
188
207
13606 fingerings
208
13801 intelligibility
209
13803 teethe
210
13901 scholastics
211
16201 wallet
212
16202 parters
213
16302 quitter
214
16303 neat
215
16304 Steinberg
216
18001 jarring
217
18002 tinily
218
18004 persist
189
219
18007 fanatic
220
18008 measures
221
18009 rightfulness
222
18012 impulsive
223
18013 starlet
224
18014 terminators
225
18015 untying
190
226
18017 featherweight
227
18018 pessimist
228
18021 lawgiver
229
18022 stated
230
18023 readable
231
18026 motors
232
18027 interrogate
233
18028 pests
234
18029 stairway
235
18032 testicle
236
18033 Parsifal
237
18034 leavings
238
18035 postulation
239
18036 squeaking
240
18038 leftover
241
18039 whiteners
242
18041 Punjab
243
18042 Merritt
244
18043 Quixotism
245
18044 sweetish
246
18046 scornfully
247
18051 sureties
248
18052 puddings
191
249
18054 fetters
250
18056 incurring
251
18058 pithed
252
18060 Miles
253
18061 trimmings
254
18101 tragedies
255
18102 skulking
192
256
18103 flint
193
257
18104 flopping
258
18201 relaxing
259
18202 offload
260
18402 suites
261
18403 lists
262
18405 multilayer
263
18406 standardizes
264
18409 Judas
265
18601 vacuuming
266
18603 humanness
267
18801 inch
268
18802 Weissmuller
269
18803 irresponsibly
270
18804 luckily
271
18806 medical
272
18808 subschema
273
18810 Micronesia
274
18811 repetitions
275
19101 ventilate
276
19102 pityingly
277
19103 interdependent
278
19201 Graves
279
30501 neonatal
280
30504 honoring
281
31901 realtor
194
282
36002 funereal
283
36005 sorters
284
38002 lectured
285
38004 Hawaii
286
38006 hushes
287
38007 Simla
288
38008 reporters
289
38011 groupings
290
38015 Taoism
195
291
38017 fetched
292
38101 rusting
293
38102 galling
294
38103 obliterates
295
38104 traitor
296
38201 resumes
297
38203 terminator
298
38204 gritty
196
299
38205 firearm
300
38206 minima
301
38208 Selfridge
302
42802 witchcraft
303
48001 Manhattanize
304
48002 imprint
305
48003 peeked
306
48004 swelling
307
48005 interrelationships
308
48006 riser
309
48007 Gandhian
310
48008 peacock
311
48102 kanji
312
48202 scarf
313
48204 insolence
314
48205 syndicate
315
48302 imperial
316
48304 railway
317
48305 validate
318
48401 normalizes
319
48405 schemer
320
48407 Kline
321
48409 partridges
322
48411 recruited
323
48602 praised
324
48604 linear
325
48801 quagmire
326
50901 western
327
51201 relishing
328
56002 serving
329
56003 scheduling
330
56004 lore
197
331
58004 Fenton
332
58101 husky
333
58102 impelling
334
58103 undoes
335
58111 squeezes
336
58113 rudeness
337
58118 mushrooms
338
58121 pellagra
339
58124 Huffman
340
58125 Majorca
341
58126 governing
342
58127 gadfly
343
58128 reassigned
344
58201 intentness
345
58303 psychic
346
58304 squabbled
347
58607 subdirectory
348
88302 Gothicism
198
349
88303 feminine
350
88305 metaphysically
351
88402 sanding
352
98025 Miltonism
199
353
186002 freakish
354
186102 index
355
186302 straight
200
356
188007 flurried
357
188011 gentleman
358
188012 gifted
359
188013 Shanghais
360
188014 sportswriting
361
188015 sloping
362
188016 navies
363
188017 leaflet
364
188102 shooter
365
188103 Joplin
366
188503 swaying
367
188504 Goldstine
201
368
188505 fitting
369
190701 Norwalk
370
196101 sating
371
196103 icon
372
196104 throttles
373
196203 priceless
374
198001 publicly
375
198003 incidentals
376
198005 mumbles
202
377
198006 furthermore
378
198008 parametrized
379
198009 registration
380
198010 sadly
381
198011 positioning
382
198014 hoarder
383
198017 rains
384
198018 workers
385
198019 sags
386
198020 unplug
387
198021 garage
388
198024 specifics
389
198101 Teresa
390
198103 Winsett
391
198204 resplendent
392
198401 sews
393
198402 participated
394
198403 Simon
203
395
202301 Fitzpatrick
396
202303 misted
397
202304 textures
398
202305 save
399
202602 rightful
400
202606 Lizzy
401
202904 Hornblower
402
202907 vests
403
202908 indulgences
404
203101 infallibly
405
203103 unwilling
406
203105 spools
407
203402 overestimating
408
203404 ineffective
409
203901 humiliation
410
203902 sophomore
411
203903 star
412
203906 rifles
413
203909 indulge
414
206201 languages
415
206203 percentage
416
206207 specification
417
206208 regimented
418
208002 pictures
419
208003 serpents
420
208008 realized
421
208009 mayoral
422
208010 opaquely
423
208011 hostess
204
424
208101 fiftieth
425
208102 incorrectly
426
208104 stranglings
427
208105 mixture
428
208111 similarities
205
429
208113 freest
430
208201 Greenberg
431
208203 tinting
432
208302 warm
433
208305 smoothed
434
208402 Romano
435
208501 securing
436
208505 heiress
437
210101 inform
438
210103 universals
439
210104 Kinsey
440
210105 ravines
441
213201 relatively
442
213203 pressure
443
213206 rightfully
444
213207 mechanizing
445
216101 Latinizes
446
216103 timesharing
447
218003 males
448
218004 shapelessly
449
218006 mastering
450
218007 Newtonian
206
451
218008 finishers
452
218010 teem
453
218011 kiting
454
218020 stodgy
207
455
218022 feed
456
218023 guitars
457
218101 store
458
218103 Pyle
459
218201 Saxony
460
218202 serializations
461
218203 Peruvian
462
218204 taxonomically
463
218205 kingdom
464
218301 stint
465
218302 Sault
208
466
218401 faithful
467
218402 Ganymede
468
218403 tidiness
469
218404 gainful
470
218408 Tipperary
471
218409 tropics
472
218410 theorizers
473
226001 renew
474
226003 terminal
475
226004 Hegelian
476
226005 hypothesizer
477
226006 warningly
478
226007 journalizing
479
226008 nested
480
226203 Lars
481
226204 saplings
209
482
226205 foothill
483
226206 labeled
484
226207 imperiously
485
226208 reporters
210
486
226209 furnishings
487
226210 precipitable
488
228004 Stalin
489
228006 ripeness
490
228008 unruly
491
228009 mournfulness
492
228108 slaughter
493
228109 Sabine
494
228110 handy
495
228111 rural
496
228112 organizer
497
228113 shipyard
498
228115 inaccuracy
499
228116 rules
500
228117 juveniles
501
228119 investigations
502
228120 stabilizes
503
228121 seminaries
504
228122 Hunter
505
228201 sporty
506
228206 test
507
228207 weasels
508
228209 tempering
509
228211 Galatean
510
228212 techniques
511
228214 veranda
512
228301 severely
211
513
228306 forthcoming
514
228307 guides
515
228308 vanish
516
228309 lied
517
228310 sawtooth
212
518
228311 fated
519
228312 gradually
520
228313 widens
521
228314 preclude
522
230302 percentage
523
18062 humility
524
230501 gleaning
525
230502 petted
526
230504 minion
527
230505 marginal
528
230507 measures
529
230508 precaution
530
230509 repelled
531
230901 primary
532
230904 navigate
533
230905 spatial
534
230906 Gurkha
535
230907 meanwhile
536
230908 Melinda
537
230911 previewing
538
230912 glut
539
230913 unaffected
540
230914 inmate
541
231304 mineral
542
231305 impending
543
231306 meditation
544
231307 ideas
545
231308 miniaturizes
546
231309 lewdly
547
231310 title
548
231311 youthfulness
213
549
231315 freezes
214
550
232102 forgivably
551
232103 reduce
552
232104 McGovern
553
232105 Nazis
554
232107 socializes
555
232601 Kevin
556
232602 uncovering
557
232606 raining
558
232607 infest
559
232609 minting
560
236105 roped
561
236106 waltz
562
236107 Lillian
563
236108 repressions
564
236110 noncritical
565
236111 lithograph
566
236112 spongers
567
236113 parenthood
568
238005 posed
569
238006 instruments
215
570
238007 filial
216
571
238008 fixedly
572
238009 relives
573
238010 Pandora
574
238011 watering
575
238012 ungrateful
576
238013 secures
577
228123 poison
578
218206 presentation
579
150401 Kantian
217
580
select fld3 from t2 where fld3 like "L%" and fld3 = "ok";
218
581
fld3
219
582
select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");
277
640
0
278
641
select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%";
279
642
fld3 period
280
overlay 9410
643
obliterates 9410
281
644
offload 9410
282
obliterates 9410
283
overestimating 9410
284
645
opaquely 9410
285
646
organizer 9410
647
overestimating 9410
648
overlay 9410
649
Pandora 9410
650
parametrized 9410
651
parenthood 9410
652
Parsifal 9410
653
parters 9410
654
participated 9410
655
partridges 9410
656
peacock 9410
657
peeked 9410
658
pellagra 9410
659
percentage 9410
660
persist 9410
661
perturb 9410
662
Peruvian 9410
663
pessimist 9410
664
pests 9410
665
petted 9410
666
pictures 9410
667
pithed 9410
668
pityingly 9410
669
poison 9410
670
posed 9410
671
positioning 9410
672
postulation 9410
673
praised 9410
674
precaution 9410
675
precipitable 9410
676
preclude 9410
677
presentation 9410
678
pressure 9410
679
previewing 9410
680
priceless 9410
681
primary 9410
682
psychic 9410
683
publicly 9410
684
puddings 9410
685
Punjab 9410
686
Pyle 9410
687
quagmire 9410
688
quitter 9410
689
Quixotism 9410
690
railway 9410
691
raining 9410
692
rains 9410
693
ravines 9410
694
readable 9410
695
realized 9410
696
realtor 9410
697
reassigned 9410
698
recruited 9410
699
reduce 9410
700
regimented 9410
701
registration 9410
702
relatively 9410
703
relaxing 9410
704
relishing 9410
705
relives 9410
706
renew 9410
707
repelled 9410
708
repetitions 9410
709
reporters 9410
710
repressions 9410
711
resplendent 9410
712
resumes 9410
713
rifles 9410
714
rightful 9410
715
rightfully 9410
716
rightfulness 9410
717
ripeness 9410
718
riser 9410
719
Romano 9410
720
Romans 9410
721
roped 9410
722
rudeness 9410
723
rules 9410
724
rural 9410
725
rusting 9410
726
Sabine 9410
727
sadly 9410
728
sags 9410
729
sanding 9410
730
saplings 9410
731
sating 9410
732
Sault 9410
733
save 9410
734
sawtooth 9410
735
Saxony 9410
736
scarf 9410
737
scatterbrain 9410
738
scheduling 9410
739
schemer 9410
740
scholastics 9410
741
scornfully 9410
742
secures 9410
743
securing 9410
744
Selfridge 9410
745
seminaries 9410
746
serializations 9410
747
serpents 9410
748
serving 9410
749
severely 9410
750
sews 9410
751
Shanghais 9410
752
shapelessly 9410
753
shipyard 9410
754
shooter 9410
755
similarities 9410
756
Simla 9410
757
Simon 9410
758
skulking 9410
759
slaughter 9410
760
sloping 9410
761
smoothed 9410
762
snatching 9410
763
socializes 9410
764
sophomore 9410
765
sorters 9410
766
spatial 9410
767
specification 9410
768
specifics 9410
769
spongers 9410
770
spools 9410
771
sportswriting 9410
772
sporty 9410
773
squabbled 9410
774
squeaking 9410
775
squeezes 9410
776
stabilizes 9410
777
stairway 9410
778
Stalin 9410
779
standardizes 9410
780
star 9410
781
starlet 9410
782
stated 9410
783
Steinberg 9410
784
stint 9410
785
stodgy 9410
786
store 9410
787
straight 9410
788
stranglings 9410
789
subdirectory 9410
790
subjective 9410
791
subschema 9410
792
succumbed 9410
793
suites 9410
794
sumac 9410
795
sureties 9410
796
swaying 9410
797
sweetish 9410
798
swelling 9410
799
syndicate 9410
800
Taoism 9410
801
taxonomically 9410
802
techniques 9410
803
teem 9410
804
teethe 9410
805
tempering 9410
806
Teresa 9410
807
terminal 9410
808
terminator 9410
809
terminators 9410
810
test 9410
811
testicle 9410
812
textures 9410
813
theorizers 9410
814
throttles 9410
815
tidiness 9410
816
timesharing 9410
817
tinily 9410
818
tinting 9410
819
Tipperary 9410
820
title 9410
821
tragedies 9410
822
traitor 9410
823
trimmings 9410
824
tropics 9410
825
unaffected 9410
826
uncovering 9410
827
undoes 9410
828
ungrateful 9410
829
universals 9410
830
unplug 9410
831
unruly 9410
832
untying 9410
833
unwilling 9410
834
vacuuming 9410
835
validate 9410
836
vanish 9410
837
ventilate 9410
838
veranda 9410
839
vests 9410
840
wallet 9410
841
waltz 9410
842
warm 9410
843
warningly 9410
844
watering 9410
845
weasels 9410
846
Weissmuller 9410
847
western 9410
848
whiteners 9410
849
widens 9410
850
Winsett 9410
851
witchcraft 9410
852
workers 9410
853
Wotan 9410
854
yelped 9410
855
youthfulness 9410
286
856
select distinct fld3 from t2 where companynr = 34 order by fld3;
287
857
fld3
288
858
absentee
357
927
vest
358
928
select distinct fld3 from t2 limit 10;
359
929
fld3
360
Omaha
361
breaking
362
Romans
363
intercepted
364
bewilderingly
365
astound
366
admonishing
367
sumac
368
flanking
369
combed
930
abates
931
abiding
932
Abraham
933
abrogating
934
absentee
935
abut
936
accessed
937
accruing
938
accumulating
939
accuracies
370
940
select distinct fld3 from t2 having fld3 like "A%" limit 10;
371
941
fld3
372
942
abates
447
1017
a
448
1018
aba
449
1019
abi
450
abr
1020
Abr
451
1021
abs
452
1022
abu
453
1023
acc
469
1039
adj
470
1040
select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10;
471
1041
a
472
ast
473
adm
474
ann
475
agi
476
afi
477
amm
478
Aug
479
aud
480
att
481
Ado
1042
aba
1043
abi
1044
Abr
1045
abs
1046
abu
1047
acc
1048
acq
1049
acu
1050
Ade
1051
adj
482
1052
create table t3 (
483
1053
period int not null,
484
1054
name char(32) not null,
487
1057
price2 double(11,0),
488
1058
key (period),
489
1059
key (name)
490
);
1060
) engine=myisam;
491
1061
create temporary table tmp engine = myisam select * from t3;
492
1062
insert into t3 select * from tmp;
493
1063
insert into tmp select * from t3;
597
1167
fld3
598
1168
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
599
1169
id select_type table type possible_keys key key_len ref rows Extra
600
1 SIMPLE t2 ALL fld1 NULL NULL NULL 1275 Using where; Using temporary; Using filesort
1170
1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
601
1171
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using index
602
1172
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
603
1173
id select_type table type possible_keys key key_len ref rows Extra
604
1 SIMPLE t1 ALL period NULL NULL NULL 42017 Using temporary; Using filesort
605
1 SIMPLE t3 ref period period 4 test.t1.period 21008
1174
1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
1175
1 SIMPLE t3 ref period period 4 test.t1.period 4181
606
1176
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
607
1177
id select_type table type possible_keys key key_len ref rows Extra
608
1178
1 SIMPLE t3 index period period 4 NULL 1
609
1 SIMPLE t1 ref period period 4 test.t3.period 21008
1179
1 SIMPLE t1 ref period period 4 test.t3.period 4181
610
1180
explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
611
1181
id select_type table type possible_keys key key_len ref rows Extra
612
1182
1 SIMPLE t1 index period period 4 NULL 1
613
1 SIMPLE t3 ref period period 4 test.t1.period 21008
1183
1 SIMPLE t3 ref period period 4 test.t1.period 4181
614
1184
select period from t1;
615
1185
period
616
1186
9410
1183
1753
uncovering 9410
1184
1754
chews 9410
1185
1755
appendixes 9410
1756
appendixes 9410
1757
appendixes 9410
1758
appendixes 9410
1759
appendixes 9410
1760
appendixes 9410
1186
1761
raining 9410
1187
1762
infest 9410
1188
1763
compartment 9410
1211
1786
encompasses 9410
1212
1787
presentation 9410
1213
1788
Kantian 9410
1214
appendixes 9410
1215
appendixes 9410
1216
appendixes 9410
1217
appendixes 9410
1218
appendixes 9410
1219
1789
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;
1220
1790
fld3 period price price2
1221
1791
admonishing 1002 28357832 8723648
1283
1853
18601 vacuuming 1001 5987435 234724
1284
1854
18801 inch 1001 5987435 234724
1285
1855
18811 repetitions 1001 5987435 234724
1856
19101 ventilate 1001 5987435 234724
1857
19201 Graves 1001 5987435 234724
1858
30501 neonatal 1001 5987435 234724
1859
31901 realtor 1001 5987435 234724
1860
36001 elite 1001 5987435 234724
1861
38001 Conley 1001 5987435 234724
1862
38011 groupings 1001 5987435 234724
1863
38101 rusting 1001 5987435 234724
1864
38201 resumes 1001 5987435 234724
1286
1865
create table t4 (
1287
1866
companynr int NOT NULL default '00',
1288
1867
companyname char(30) NOT NULL default '',
1356
1935
1199
1357
1936
explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1358
1937
id select_type table type possible_keys key key_len ref rows Extra
1359
1 SIMPLE t2 ALL NULL NULL NULL NULL 1276
1938
1 SIMPLE t2 ALL NULL NULL NULL NULL 1200
1360
1939
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr 1 Using where; Not exists
1361
1940
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
1362
1941
id select_type table type possible_keys key key_len ref rows Extra
1363
1942
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1364
1 SIMPLE t2 ALL NULL NULL NULL NULL 1276 Using where; Not exists
1943
1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists
1365
1944
select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1366
1945
companynr companyname
1367
1946
select count(*) from t2 left join t4 using (companynr) where companynr is not null;
1376
1955
delete from t2 where fld1=999999;
1377
1956
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
1378
1957
id select_type table type possible_keys key key_len ref rows Extra
1379
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275 Using where
1958
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1380
1959
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr 1
1381
1960
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
1382
1961
id select_type table type possible_keys key key_len ref rows Extra
1383
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275 Using where
1962
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1384
1963
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr 1
1385
1964
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
1386
1965
id select_type table type possible_keys key key_len ref rows Extra
1387
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275 Using where
1966
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1388
1967
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr 1
1389
1968
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
1390
1969
id select_type table type possible_keys key key_len ref rows Extra
1391
1970
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1392
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275
1971
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1393
1972
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
1394
1973
id select_type table type possible_keys key key_len ref rows Extra
1395
1974
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1396
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275
1975
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1397
1976
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
1398
1977
id select_type table type possible_keys key key_len ref rows Extra
1399
1978
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1400
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275
1979
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1401
1980
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
1402
1981
id select_type table type possible_keys key key_len ref rows Extra
1403
1982
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1404
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275 Using where
1983
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1405
1984
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
1406
1985
id select_type table type possible_keys key key_len ref rows Extra
1407
1986
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12
1408
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275 Using where
1987
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1409
1988
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
1410
1989
id select_type table type possible_keys key key_len ref rows Extra
1411
1990
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1412
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275 Using where
1991
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1413
1992
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
1414
1993
id select_type table type possible_keys key key_len ref rows Extra
1415
1994
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1416
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275
1995
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1417
1996
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
1418
1997
id select_type table type possible_keys key key_len ref rows Extra
1419
1998
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1420
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275
1999
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1421
2000
explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
1422
2001
id select_type table type possible_keys key key_len ref rows Extra
1423
2002
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where
1424
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275
2003
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1425
2004
select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1426
2005
companynr companynr
1427
2006
37 36
1429
2008
explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1430
2009
id select_type table type possible_keys key key_len ref rows Extra
1431
2010
1 SIMPLE t4 index NULL PRIMARY 4 NULL 12 Using index; Using temporary
1432
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275 Using where; Using join buffer
2011
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer
1433
2012
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;
1434
2013
fld1 companynr fld3 period
1435
2014
38008 37 reporters 1008
1503
2082
70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1504
2083
explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1505
2084
id select_type table type possible_keys key key_len ref rows filtered Extra
1506
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275 100.00 Using where
2085
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where
1507
2086
Warnings:
1508
2087
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` <> ''))
1509
2088
select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
1947
2526
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1948
2527
explain select fld3 from t2 where fld1=fld1;
1949
2528
id select_type table type possible_keys key key_len ref rows Extra
1950
1 SIMPLE t2 ALL NULL NULL NULL NULL 1275
2529
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1951
2530
select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;
1952
2531
companynr fld1
1953
2532
34 250501
1999
2578
4181
2000
2579
explain select min(fld1),max(fld1),count(*) from t2;
2001
2580
id select_type table type possible_keys key key_len ref rows Extra
2002
1 SIMPLE t2 index NULL PRIMARY 4 NULL 1275
2581
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2003
2582
select min(fld1),max(fld1),count(*) from t2;
2004
2583
min(fld1) max(fld1) count(*)
2005
2584
0 1232609 1199
2085
2664
Field Type Collation Null Key Default Extra Privileges Comment
2086
2665
show keys from t2;
2087
2666
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
2088
t2 0 PRIMARY 1 auto A 1275 NULL NULL BTREE
2089
t2 0 fld1 1 fld1 A 1275 NULL NULL BTREE
2090
t2 1 fld3 1 fld3 A 1275 NULL NULL BTREE
2667
t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE
2668
t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE
2669
t2 1 fld3 1 fld3 A NULL NULL NULL BTREE
2091
2670
drop table t4, t3, t2, t1;
2092
2671
CREATE TABLE t1 (
2093
2672
cont_nr int NOT NULL auto_increment,
Loggerhead is a web-based interface for Breezy
Version: 2.0.1