1drop table if exists t1,t2,t3,t4; 2CREATE TABLE t1 ( 3Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, 4Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL 5); 6INSERT INTO t1 VALUES (9410,9412); 7select period from t1; 8period 99410 10select * from t1; 11Period Varor_period 129410 9412 13select t1.* from t1; 14Period Varor_period 159410 9412 16CREATE TABLE t2 ( 17auto int not null auto_increment, 18fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL, 19companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL, 20fld3 char(30) DEFAULT '' NOT NULL, 21fld4 char(35) DEFAULT '' NOT NULL, 22fld5 char(35) DEFAULT '' NOT NULL, 23fld6 char(4) DEFAULT '' NOT NULL, 24UNIQUE fld1 (fld1), 25KEY fld3 (fld3), 26PRIMARY KEY (auto) 27); 28select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%"; 29fld3 30imaginable 31select fld3 from t2 where fld3 like "%cultivation" ; 32fld3 33cultivation 34select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3; 35fld3 companynr 36concoct 58 37druggists 58 38engrossing 58 39Eurydice 58 40exclaimers 58 41ferociousness 58 42hopelessness 58 43Huey 58 44imaginable 58 45judges 58 46merging 58 47ostrich 58 48peering 58 49Phelps 58 50presumes 58 51Ruth 58 52sentences 58 53Shylock 58 54straggled 58 55synergy 58 56thanking 58 57tying 58 58unlocks 58 59select fld3,companynr from t2 where companynr = 58 order by fld3; 60fld3 companynr 61concoct 58 62druggists 58 63engrossing 58 64Eurydice 58 65exclaimers 58 66ferociousness 58 67hopelessness 58 68Huey 58 69imaginable 58 70judges 58 71merging 58 72ostrich 58 73peering 58 74Phelps 58 75presumes 58 76Ruth 58 77sentences 58 78Shylock 58 79straggled 58 80synergy 58 81thanking 58 82tying 58 83unlocks 58 84select fld3 from t2 order by fld3 desc limit 10; 85fld3 86youthfulness 87yelped 88Wotan 89workers 90Witt 91witchcraft 92Winsett 93Willy 94willed 95wildcats 96select fld3 from t2 order by fld3 desc limit 5; 97fld3 98youthfulness 99yelped 100Wotan 101workers 102Witt 103select fld3 from t2 order by fld3 desc limit 5,5; 104fld3 105witchcraft 106Winsett 107Willy 108willed 109wildcats 110select t2.fld3 from t2 where fld3 = 'honeysuckle'; 111fld3 112honeysuckle 113select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_'; 114fld3 115honeysuckle 116select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_'; 117fld3 118honeysuckle 119select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%'; 120fld3 121honeysuckle 122select t2.fld3 from t2 where fld3 LIKE 'h%le'; 123fld3 124honeysuckle 125select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_'; 126fld3 127select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%'; 128fld3 129explain select t2.fld3 from t2 where fld3 = 'honeysuckle'; 130id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1311 SIMPLE t2 NULL ref fld3 fld3 30 const 1 100.00 Using index 132Warnings: 133Note 1003 /* select#1 */ select `test`.`t2`.`fld3` AS `fld3` from `test`.`t2` where (`test`.`t2`.`fld3` = 'honeysuckle') 134explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle'; 135id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1361 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 10.00 Using where 137Warnings: 138Note 1003 /* select#1 */ select `test`.`t2`.`fld3` AS `fld3` from `test`.`t2` IGNORE INDEX (`fld3`) where (`test`.`t2`.`fld3` = 'honeysuckle') 139explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle'; 140id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1411 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 10.00 Using where 142Warnings: 143Note 1003 /* select#1 */ select `test`.`t2`.`fld3` AS `fld3` from `test`.`t2` USE INDEX (`fld1`) where (`test`.`t2`.`fld3` = 'honeysuckle') 144explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle'; 145id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1461 SIMPLE t2 NULL ref fld3 fld3 30 const 1 100.00 Using index 147Warnings: 148Note 1003 /* select#1 */ select `test`.`t2`.`fld3` AS `fld3` from `test`.`t2` USE INDEX (`fld3`) where (`test`.`t2`.`fld3` = 'honeysuckle') 149explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; 150id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1511 SIMPLE t2 NULL ref fld3 fld3 30 const 1 100.00 Using index 152Warnings: 153Note 1003 /* select#1 */ select `test`.`t2`.`fld3` AS `fld3` from `test`.`t2` USE INDEX (`fld3`) USE INDEX (`fld1`) where (`test`.`t2`.`fld3` = 'honeysuckle') 154explain select fld3 from t2 ignore index (fld3,not_used); 155ERROR 42000: Key 'not_used' doesn't exist in table 't2' 156explain select fld3 from t2 use index (not_used); 157ERROR 42000: Key 'not_used' doesn't exist in table 't2' 158select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 159fld3 160honeysuckle 161honoring 162explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 163id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1641 SIMPLE t2 NULL range fld3 fld3 30 NULL 2 100.00 Using where; Using index 165Warnings: 166Note 1003 /* select#1 */ select `test`.`t2`.`fld3` AS `fld3` from `test`.`t2` where ((`test`.`t2`.`fld3` >= 'honeysuckle') and (`test`.`t2`.`fld3` <= 'honoring')) order by `test`.`t2`.`fld3` 167select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; 168fld1 fld3 169148504 Colombo 170068305 Colombo 171000000 nondecreasing 172select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes'; 173fld1 fld3 174232605 appendixes 1751232605 appendixes 1761232606 appendixes 1771232607 appendixes 1781232608 appendixes 1791232609 appendixes 180select fld1 from t2 where fld1=250501 or fld1="250502"; 181fld1 182250501 183250502 184explain select fld1 from t2 where fld1=250501 or fld1="250502"; 185id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1861 SIMPLE t2 NULL range fld1 fld1 4 NULL 2 100.00 Using where; Using index 187Warnings: 188Note 1003 /* select#1 */ select `test`.`t2`.`fld1` AS `fld1` from `test`.`t2` where ((`test`.`t2`.`fld1` = 250501) or (`test`.`t2`.`fld1` = '250502')) 189select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 190fld1 191250501 192250502 193250505 194250601 195explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 196id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1971 SIMPLE t2 NULL range fld1 fld1 4 NULL 4 100.00 Using where; Using index 198Warnings: 199Note 1003 /* select#1 */ select `test`.`t2`.`fld1` AS `fld1` from `test`.`t2` where ((`test`.`t2`.`fld1` = 250501) or (`test`.`t2`.`fld1` = 250502) or ((`test`.`t2`.`fld1` >= 250505) and (`test`.`t2`.`fld1` <= 250601)) or (`test`.`t2`.`fld1` between 250501 and 250502)) 200select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; 201fld1 fld3 202012001 flanking 203013602 foldout 204013606 fingerings 205018007 fanatic 206018017 featherweight 207018054 fetters 208018103 flint 209018104 flopping 210036002 funereal 211038017 fetched 212038205 firearm 213058004 Fenton 214088303 feminine 215186002 freakish 216188007 flurried 217188505 fitting 218198006 furthermore 219202301 Fitzpatrick 220208101 fiftieth 221208113 freest 222218008 finishers 223218022 feed 224218401 faithful 225226205 foothill 226226209 furnishings 227228306 forthcoming 228228311 fated 229231315 freezes 230232102 forgivably 231238007 filial 232238008 fixedly 233select fld3 from t2 where fld3 like "L%" and fld3 = "ok"; 234fld3 235select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly"); 236fld3 237Chantilly 238select fld1,fld3 from t2 where fld1 like "25050%"; 239fld1 fld3 240250501 poisoning 241250502 Iraqis 242250503 heaving 243250504 population 244250505 bomb 245select fld1,fld3 from t2 where fld1 like "25050_"; 246fld1 fld3 247250501 poisoning 248250502 Iraqis 249250503 heaving 250250504 population 251250505 bomb 252select distinct companynr from t2; 253companynr 25400 25537 25636 25750 25858 25929 26040 26153 26265 26341 26434 26568 266select distinct companynr from t2 order by companynr; 267companynr 26800 26929 27034 27136 27237 27340 27441 27550 27653 27758 27865 27968 280select distinct companynr from t2 order by companynr desc; 281companynr 28268 28365 28458 28553 28650 28741 28840 28937 29036 29134 29229 29300 294select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%"; 295fld3 period 296obliterates 9410 297offload 9410 298opaquely 9410 299organizer 9410 300overestimating 9410 301overlay 9410 302select distinct fld3 from t2 where companynr = 34 order by fld3; 303fld3 304absentee 305accessed 306ahead 307alphabetic 308Asiaticizations 309attitude 310aye 311bankruptcies 312belays 313Blythe 314bomb 315boulevard 316bulldozes 317cannot 318caressing 319charcoal 320checksumming 321chess 322clubroom 323colorful 324cosy 325creator 326crying 327Darius 328diffusing 329duality 330Eiffel 331Epiphany 332Ernestine 333explorers 334exterminated 335famine 336forked 337Gershwins 338heaving 339Hodges 340Iraqis 341Italianization 342Lagos 343landslide 344libretto 345Majorca 346mastering 347narrowed 348occurred 349offerers 350Palestine 351Peruvianizes 352pharmaceutic 353poisoning 354population 355Pygmalion 356rats 357realest 358recording 359regimented 360retransmitting 361reviver 362rouses 363scars 364sicker 365sleepwalk 366stopped 367sugars 368translatable 369uncles 370unexpected 371uprisings 372versatility 373vest 374select distinct fld3 from t2 limit 10; 375fld3 376abates 377abiding 378Abraham 379abrogating 380absentee 381abut 382accessed 383accruing 384accumulating 385accuracies 386select distinct fld3 from t2 having fld3 like "A%" limit 10; 387fld3 388abates 389abiding 390Abraham 391abrogating 392absentee 393abut 394accessed 395accruing 396accumulating 397accuracies 398select distinct substring(fld3,1,3) from t2 where fld3 like "A%"; 399substring(fld3,1,3) 400aba 401abi 402Abr 403abs 404abu 405acc 406acq 407acu 408Ade 409adj 410Adl 411adm 412Ado 413ads 414adv 415aer 416aff 417afi 418afl 419afo 420agi 421ahe 422aim 423air 424Ald 425alg 426ali 427all 428alp 429alr 430ama 431ame 432amm 433ana 434and 435ane 436Ang 437ani 438Ann 439Ant 440api 441app 442aqu 443Ara 444arc 445Arm 446arr 447Art 448Asi 449ask 450asp 451ass 452ast 453att 454aud 455Aug 456aut 457ave 458avo 459awe 460aye 461Azt 462select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10; 463a 464aba 465abi 466Abr 467abs 468abu 469acc 470acq 471acu 472Ade 473adj 474select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10; 475substring(fld3,1,3) 476aba 477abi 478Abr 479abs 480abu 481acc 482acq 483acu 484Ade 485adj 486select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10; 487a 488aba 489abi 490Abr 491abs 492abu 493acc 494acq 495acu 496Ade 497adj 498create table t3 ( 499period int not null, 500name char(32) not null, 501companynr int not null, 502price double(11,0), 503price2 double(11,0), 504key (period), 505key (name) 506); 507create temporary table tmp engine = myisam select * from t3; 508insert into t3 select * from tmp; 509insert into tmp select * from t3; 510insert into t3 select * from tmp; 511insert into tmp select * from t3; 512insert into t3 select * from tmp; 513insert into tmp select * from t3; 514insert into t3 select * from tmp; 515insert into tmp select * from t3; 516insert into t3 select * from tmp; 517insert into tmp select * from t3; 518insert into t3 select * from tmp; 519insert into tmp select * from t3; 520insert into t3 select * from tmp; 521insert into tmp select * from t3; 522insert into t3 select * from tmp; 523insert into tmp select * from t3; 524insert into t3 select * from tmp; 525alter table t3 add t2nr int not null auto_increment primary key first; 526drop table tmp; 527SET BIG_TABLES=1; 528select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; 529namn 530Abraham Abraham 531abrogating abrogating 532admonishing admonishing 533Adolph Adolph 534afield afield 535aging aging 536ammonium ammonium 537analyzable analyzable 538animals animals 539animized animized 540SET BIG_TABLES=0; 541select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; 542concat(fld3," ",fld3) 543Abraham Abraham 544abrogating abrogating 545admonishing admonishing 546Adolph Adolph 547afield afield 548aging aging 549ammonium ammonium 550analyzable analyzable 551animals animals 552animized animized 553select distinct fld5 from t2 limit 10; 554fld5 555neat 556Steinberg 557jarring 558tinily 559balled 560persist 561attainments 562fanatic 563measures 564rightfulness 565select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 566fld3 count(*) 567affixed 1 568and 1 569annoyers 1 570Anthony 1 571assayed 1 572assurers 1 573attendants 1 574bedlam 1 575bedpost 1 576boasted 1 577SET BIG_TABLES=1; 578select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 579fld3 count(*) 580affixed 1 581and 1 582annoyers 1 583Anthony 1 584assayed 1 585assurers 1 586attendants 1 587bedlam 1 588bedpost 1 589boasted 1 590SET BIG_TABLES=0; 591select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; 592fld3 repeat("a",length(fld3)) count(*) 593chancellor aaaaaaaaaa 1 594Chippewa aaaaaaaa 1 595circumference aaaaaaaaaaaaa 1 596circus aaaaaa 1 597cited aaaaa 1 598congresswoman aaaaaaaaaaaaa 1 599contrition aaaaaaaaaa 1 600corny aaaaa 1 601cultivation aaaaaaaaaaa 1 602definiteness aaaaaaaaaaaa 1 603select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2; 604companynr rtrim(space(512+companynr)) 60537 60678 607101 608154 609311 610447 611512 612select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3; 613fld3 614explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3; 615id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6161 SIMPLE t2 NULL ALL fld1 NULL NULL NULL 1199 10.00 Using where; Using temporary; Using filesort 6171 SIMPLE t3 NULL eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 100.00 Using where; Using index 618Warnings: 619Note 1003 /* select#1 */ select `test`.`t3`.`t2nr` AS `t2nr`,`test`.`t2`.`fld3` AS `fld3` from `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld1` = `test`.`t3`.`t2nr`)) order by `test`.`t3`.`t2nr`,`test`.`t2`.`fld3` 620explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; 621id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6221 SIMPLE t1 NULL ALL period NULL NULL NULL 41810 100.00 Using temporary; Using filesort 6231 SIMPLE t3 NULL ref period period 4 test.t1.period 4181 100.00 NULL 624Warnings: 625Note 1003 /* select#1 */ select `test`.`t1`.`t2nr` AS `t2nr`,`test`.`t1`.`period` AS `period`,`test`.`t1`.`name` AS `name`,`test`.`t1`.`companynr` AS `companynr`,`test`.`t1`.`price` AS `price`,`test`.`t1`.`price2` AS `price2`,`test`.`t3`.`t2nr` AS `t2nr`,`test`.`t3`.`period` AS `period`,`test`.`t3`.`name` AS `name`,`test`.`t3`.`companynr` AS `companynr`,`test`.`t3`.`price` AS `price`,`test`.`t3`.`price2` AS `price2` from `test`.`t3` `t1` join `test`.`t3` where (`test`.`t3`.`period` = `test`.`t1`.`period`) order by `test`.`t3`.`period` 626explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; 627id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6281 SIMPLE t3 NULL index period period 4 NULL 1 100.00 NULL 6291 SIMPLE t1 NULL ref period period 4 test.t3.period 4181 100.00 NULL 630Warnings: 631Note 1003 /* select#1 */ select `test`.`t1`.`t2nr` AS `t2nr`,`test`.`t1`.`period` AS `period`,`test`.`t1`.`name` AS `name`,`test`.`t1`.`companynr` AS `companynr`,`test`.`t1`.`price` AS `price`,`test`.`t1`.`price2` AS `price2`,`test`.`t3`.`t2nr` AS `t2nr`,`test`.`t3`.`period` AS `period`,`test`.`t3`.`name` AS `name`,`test`.`t3`.`companynr` AS `companynr`,`test`.`t3`.`price` AS `price`,`test`.`t3`.`price2` AS `price2` from `test`.`t3` `t1` join `test`.`t3` where (`test`.`t1`.`period` = `test`.`t3`.`period`) order by `test`.`t3`.`period` limit 10 632explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; 633id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6341 SIMPLE t1 NULL index period period 4 NULL 1 100.00 NULL 6351 SIMPLE t3 NULL ref period period 4 test.t1.period 4181 100.00 NULL 636Warnings: 637Note 1003 /* select#1 */ select `test`.`t1`.`t2nr` AS `t2nr`,`test`.`t1`.`period` AS `period`,`test`.`t1`.`name` AS `name`,`test`.`t1`.`companynr` AS `companynr`,`test`.`t1`.`price` AS `price`,`test`.`t1`.`price2` AS `price2`,`test`.`t3`.`t2nr` AS `t2nr`,`test`.`t3`.`period` AS `period`,`test`.`t3`.`name` AS `name`,`test`.`t3`.`companynr` AS `companynr`,`test`.`t3`.`price` AS `price`,`test`.`t3`.`price2` AS `price2` from `test`.`t3` `t1` join `test`.`t3` where (`test`.`t3`.`period` = `test`.`t1`.`period`) order by `test`.`t1`.`period` limit 10 638select period from t1; 639period 6409410 641select period from t1 where period=1900; 642period 643select fld3,period from t1,t2 where fld1 = 011401 order by period; 644fld3 period 645breaking 9410 646select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001; 647fld3 period 648breaking 1001 649explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period; 650id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6511 SIMPLE t2 NULL const fld1 fld1 4 const 1 100.00 NULL 6521 SIMPLE t3 NULL const PRIMARY,period PRIMARY 4 const 1 100.00 NULL 653Warnings: 654Note 1003 /* select#1 */ select 'breaking' AS `fld3`,'1001' AS `period` from `test`.`t2` join `test`.`t3` where (('11401' = 11401)) 655select fld3,period from t2,t1 where companynr*10 = 37*10; 656fld3 period 657breaking 9410 658Romans 9410 659intercepted 9410 660bewilderingly 9410 661astound 9410 662admonishing 9410 663sumac 9410 664flanking 9410 665combed 9410 666subjective 9410 667scatterbrain 9410 668Eulerian 9410 669Kane 9410 670overlay 9410 671perturb 9410 672goblins 9410 673annihilates 9410 674Wotan 9410 675snatching 9410 676concludes 9410 677laterally 9410 678yelped 9410 679grazing 9410 680Baird 9410 681celery 9410 682misunderstander 9410 683handgun 9410 684foldout 9410 685mystic 9410 686succumbed 9410 687Nabisco 9410 688fingerings 9410 689aging 9410 690afield 9410 691ammonium 9410 692boat 9410 693intelligibility 9410 694Augustine 9410 695teethe 9410 696dreaded 9410 697scholastics 9410 698audiology 9410 699wallet 9410 700parters 9410 701eschew 9410 702quitter 9410 703neat 9410 704Steinberg 9410 705jarring 9410 706tinily 9410 707balled 9410 708persist 9410 709attainments 9410 710fanatic 9410 711measures 9410 712rightfulness 9410 713capably 9410 714impulsive 9410 715starlet 9410 716terminators 9410 717untying 9410 718announces 9410 719featherweight 9410 720pessimist 9410 721daughter 9410 722decliner 9410 723lawgiver 9410 724stated 9410 725readable 9410 726attrition 9410 727cascade 9410 728motors 9410 729interrogate 9410 730pests 9410 731stairway 9410 732dopers 9410 733testicle 9410 734Parsifal 9410 735leavings 9410 736postulation 9410 737squeaking 9410 738contrasted 9410 739leftover 9410 740whiteners 9410 741erases 9410 742Punjab 9410 743Merritt 9410 744Quixotism 9410 745sweetish 9410 746dogging 9410 747scornfully 9410 748bellow 9410 749bills 9410 750cupboard 9410 751sureties 9410 752puddings 9410 753fetters 9410 754bivalves 9410 755incurring 9410 756Adolph 9410 757pithed 9410 758Miles 9410 759trimmings 9410 760tragedies 9410 761skulking 9410 762flint 9410 763flopping 9410 764relaxing 9410 765offload 9410 766suites 9410 767lists 9410 768animized 9410 769multilayer 9410 770standardizes 9410 771Judas 9410 772vacuuming 9410 773dentally 9410 774humanness 9410 775inch 9410 776Weissmuller 9410 777irresponsibly 9410 778luckily 9410 779culled 9410 780medical 9410 781bloodbath 9410 782subschema 9410 783animals 9410 784Micronesia 9410 785repetitions 9410 786Antares 9410 787ventilate 9410 788pityingly 9410 789interdependent 9410 790Graves 9410 791neonatal 9410 792chafe 9410 793honoring 9410 794realtor 9410 795elite 9410 796funereal 9410 797abrogating 9410 798sorters 9410 799Conley 9410 800lectured 9410 801Abraham 9410 802Hawaii 9410 803cage 9410 804hushes 9410 805Simla 9410 806reporters 9410 807Dutchman 9410 808descendants 9410 809groupings 9410 810dissociate 9410 811coexist 9410 812Beebe 9410 813Taoism 9410 814Connally 9410 815fetched 9410 816checkpoints 9410 817rusting 9410 818galling 9410 819obliterates 9410 820traitor 9410 821resumes 9410 822analyzable 9410 823terminator 9410 824gritty 9410 825firearm 9410 826minima 9410 827Selfridge 9410 828disable 9410 829witchcraft 9410 830betroth 9410 831Manhattanize 9410 832imprint 9410 833peeked 9410 834swelling 9410 835interrelationships 9410 836riser 9410 837Gandhian 9410 838peacock 9410 839bee 9410 840kanji 9410 841dental 9410 842scarf 9410 843chasm 9410 844insolence 9410 845syndicate 9410 846alike 9410 847imperial 9410 848convulsion 9410 849railway 9410 850validate 9410 851normalizes 9410 852comprehensive 9410 853chewing 9410 854denizen 9410 855schemer 9410 856chronicle 9410 857Kline 9410 858Anatole 9410 859partridges 9410 860brunch 9410 861recruited 9410 862dimensions 9410 863Chicana 9410 864announced 9410 865praised 9410 866employing 9410 867linear 9410 868quagmire 9410 869western 9410 870relishing 9410 871serving 9410 872scheduling 9410 873lore 9410 874eventful 9410 875arteriole 9410 876disentangle 9410 877cured 9410 878Fenton 9410 879avoidable 9410 880drains 9410 881detectably 9410 882husky 9410 883impelling 9410 884undoes 9410 885evened 9410 886squeezes 9410 887destroyer 9410 888rudeness 9410 889beaner 9410 890boorish 9410 891Everhart 9410 892encompass 9410 893mushrooms 9410 894Alison 9410 895externally 9410 896pellagra 9410 897cult 9410 898creek 9410 899Huffman 9410 900Majorca 9410 901governing 9410 902gadfly 9410 903reassigned 9410 904intentness 9410 905craziness 9410 906psychic 9410 907squabbled 9410 908burlesque 9410 909capped 9410 910extracted 9410 911DiMaggio 9410 912exclamation 9410 913subdirectory 9410 914Gothicism 9410 915feminine 9410 916metaphysically 9410 917sanding 9410 918Miltonism 9410 919freakish 9410 920index 9410 921straight 9410 922flurried 9410 923denotative 9410 924coming 9410 925commencements 9410 926gentleman 9410 927gifted 9410 928Shanghais 9410 929sportswriting 9410 930sloping 9410 931navies 9410 932leaflet 9410 933shooter 9410 934Joplin 9410 935babies 9410 936assails 9410 937admiring 9410 938swaying 9410 939Goldstine 9410 940fitting 9410 941Norwalk 9410 942analogy 9410 943deludes 9410 944cokes 9410 945Clayton 9410 946exhausts 9410 947causality 9410 948sating 9410 949icon 9410 950throttles 9410 951communicants 9410 952dehydrate 9410 953priceless 9410 954publicly 9410 955incidentals 9410 956commonplace 9410 957mumbles 9410 958furthermore 9410 959cautioned 9410 960parametrized 9410 961registration 9410 962sadly 9410 963positioning 9410 964babysitting 9410 965eternal 9410 966hoarder 9410 967congregates 9410 968rains 9410 969workers 9410 970sags 9410 971unplug 9410 972garage 9410 973boulder 9410 974specifics 9410 975Teresa 9410 976Winsett 9410 977convenient 9410 978buckboards 9410 979amenities 9410 980resplendent 9410 981sews 9410 982participated 9410 983Simon 9410 984certificates 9410 985Fitzpatrick 9410 986Evanston 9410 987misted 9410 988textures 9410 989save 9410 990count 9410 991rightful 9410 992chaperone 9410 993Lizzy 9410 994clenched 9410 995effortlessly 9410 996accessed 9410 997beaters 9410 998Hornblower 9410 999vests 9410 1000indulgences 9410 1001infallibly 9410 1002unwilling 9410 1003excrete 9410 1004spools 9410 1005crunches 9410 1006overestimating 9410 1007ineffective 9410 1008humiliation 9410 1009sophomore 9410 1010star 9410 1011rifles 9410 1012dialysis 9410 1013arriving 9410 1014indulge 9410 1015clockers 9410 1016languages 9410 1017Antarctica 9410 1018percentage 9410 1019ceiling 9410 1020specification 9410 1021regimented 9410 1022ciphers 9410 1023pictures 9410 1024serpents 9410 1025allot 9410 1026realized 9410 1027mayoral 9410 1028opaquely 9410 1029hostess 9410 1030fiftieth 9410 1031incorrectly 9410 1032decomposition 9410 1033stranglings 9410 1034mixture 9410 1035electroencephalography 9410 1036similarities 9410 1037charges 9410 1038freest 9410 1039Greenberg 9410 1040tinting 9410 1041expelled 9410 1042warm 9410 1043smoothed 9410 1044deductions 9410 1045Romano 9410 1046bitterroot 9410 1047corset 9410 1048securing 9410 1049environing 9410 1050cute 9410 1051Crays 9410 1052heiress 9410 1053inform 9410 1054avenge 9410 1055universals 9410 1056Kinsey 9410 1057ravines 9410 1058bestseller 9410 1059equilibrium 9410 1060extents 9410 1061relatively 9410 1062pressure 9410 1063critiques 9410 1064befouled 9410 1065rightfully 9410 1066mechanizing 9410 1067Latinizes 9410 1068timesharing 9410 1069Aden 9410 1070embassies 9410 1071males 9410 1072shapelessly 9410 1073mastering 9410 1074Newtonian 9410 1075finishers 9410 1076abates 9410 1077teem 9410 1078kiting 9410 1079stodgy 9410 1080feed 9410 1081guitars 9410 1082airships 9410 1083store 9410 1084denounces 9410 1085Pyle 9410 1086Saxony 9410 1087serializations 9410 1088Peruvian 9410 1089taxonomically 9410 1090kingdom 9410 1091stint 9410 1092Sault 9410 1093faithful 9410 1094Ganymede 9410 1095tidiness 9410 1096gainful 9410 1097contrary 9410 1098Tipperary 9410 1099tropics 9410 1100theorizers 9410 1101renew 9410 1102already 9410 1103terminal 9410 1104Hegelian 9410 1105hypothesizer 9410 1106warningly 9410 1107journalizing 9410 1108nested 9410 1109Lars 9410 1110saplings 9410 1111foothill 9410 1112labeled 9410 1113imperiously 9410 1114reporters 9410 1115furnishings 9410 1116precipitable 9410 1117discounts 9410 1118excises 9410 1119Stalin 9410 1120despot 9410 1121ripeness 9410 1122Arabia 9410 1123unruly 9410 1124mournfulness 9410 1125boom 9410 1126slaughter 9410 1127Sabine 9410 1128handy 9410 1129rural 9410 1130organizer 9410 1131shipyard 9410 1132civics 9410 1133inaccuracy 9410 1134rules 9410 1135juveniles 9410 1136comprised 9410 1137investigations 9410 1138stabilizes 9410 1139seminaries 9410 1140Hunter 9410 1141sporty 9410 1142test 9410 1143weasels 9410 1144CERN 9410 1145tempering 9410 1146afore 9410 1147Galatean 9410 1148techniques 9410 1149error 9410 1150veranda 9410 1151severely 9410 1152Cassites 9410 1153forthcoming 9410 1154guides 9410 1155vanish 9410 1156lied 9410 1157sawtooth 9410 1158fated 9410 1159gradually 9410 1160widens 9410 1161preclude 9410 1162evenhandedly 9410 1163percentage 9410 1164disobedience 9410 1165humility 9410 1166gleaning 9410 1167petted 9410 1168bloater 9410 1169minion 9410 1170marginal 9410 1171apiary 9410 1172measures 9410 1173precaution 9410 1174repelled 9410 1175primary 9410 1176coverings 9410 1177Artemia 9410 1178navigate 9410 1179spatial 9410 1180Gurkha 9410 1181meanwhile 9410 1182Melinda 9410 1183Butterfield 9410 1184Aldrich 9410 1185previewing 9410 1186glut 9410 1187unaffected 9410 1188inmate 9410 1189mineral 9410 1190impending 9410 1191meditation 9410 1192ideas 9410 1193miniaturizes 9410 1194lewdly 9410 1195title 9410 1196youthfulness 9410 1197creak 9410 1198Chippewa 9410 1199clamored 9410 1200freezes 9410 1201forgivably 9410 1202reduce 9410 1203McGovern 9410 1204Nazis 9410 1205epistle 9410 1206socializes 9410 1207conceptions 9410 1208Kevin 9410 1209uncovering 9410 1210chews 9410 1211appendixes 9410 1212appendixes 9410 1213appendixes 9410 1214appendixes 9410 1215appendixes 9410 1216appendixes 9410 1217raining 9410 1218infest 9410 1219compartment 9410 1220minting 9410 1221ducks 9410 1222roped 9410 1223waltz 9410 1224Lillian 9410 1225repressions 9410 1226chillingly 9410 1227noncritical 9410 1228lithograph 9410 1229spongers 9410 1230parenthood 9410 1231posed 9410 1232instruments 9410 1233filial 9410 1234fixedly 9410 1235relives 9410 1236Pandora 9410 1237watering 9410 1238ungrateful 9410 1239secures 9410 1240poison 9410 1241dusted 9410 1242encompasses 9410 1243presentation 9410 1244Kantian 9410 1245select 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; 1246fld3 period price price2 1247admonishing 1002 28357832 8723648 1248analyzable 1002 28357832 8723648 1249annihilates 1001 5987435 234724 1250Antares 1002 28357832 8723648 1251astound 1001 5987435 234724 1252audiology 1001 5987435 234724 1253Augustine 1002 28357832 8723648 1254Baird 1002 28357832 8723648 1255bewilderingly 1001 5987435 234724 1256breaking 1001 5987435 234724 1257Conley 1001 5987435 234724 1258dentally 1002 28357832 8723648 1259dissociate 1002 28357832 8723648 1260elite 1001 5987435 234724 1261eschew 1001 5987435 234724 1262Eulerian 1001 5987435 234724 1263flanking 1001 5987435 234724 1264foldout 1002 28357832 8723648 1265funereal 1002 28357832 8723648 1266galling 1002 28357832 8723648 1267Graves 1001 5987435 234724 1268grazing 1001 5987435 234724 1269groupings 1001 5987435 234724 1270handgun 1001 5987435 234724 1271humility 1002 28357832 8723648 1272impulsive 1002 28357832 8723648 1273inch 1001 5987435 234724 1274intelligibility 1001 5987435 234724 1275jarring 1001 5987435 234724 1276lawgiver 1001 5987435 234724 1277lectured 1002 28357832 8723648 1278Merritt 1002 28357832 8723648 1279neonatal 1001 5987435 234724 1280offload 1002 28357832 8723648 1281parters 1002 28357832 8723648 1282pityingly 1002 28357832 8723648 1283puddings 1002 28357832 8723648 1284Punjab 1001 5987435 234724 1285quitter 1002 28357832 8723648 1286realtor 1001 5987435 234724 1287relaxing 1001 5987435 234724 1288repetitions 1001 5987435 234724 1289resumes 1001 5987435 234724 1290Romans 1002 28357832 8723648 1291rusting 1001 5987435 234724 1292scholastics 1001 5987435 234724 1293skulking 1002 28357832 8723648 1294stated 1002 28357832 8723648 1295suites 1002 28357832 8723648 1296sureties 1001 5987435 234724 1297testicle 1002 28357832 8723648 1298tinily 1002 28357832 8723648 1299tragedies 1001 5987435 234724 1300trimmings 1001 5987435 234724 1301vacuuming 1001 5987435 234724 1302ventilate 1001 5987435 234724 1303wallet 1001 5987435 234724 1304Weissmuller 1002 28357832 8723648 1305Wotan 1002 28357832 8723648 1306select t2.fld1,fld3,period,price,price2 from t2,t3 where t2.fld1>= 18201 and t2.fld1 <= 18811 and t2.fld1=t3.t2nr and period = 1001 and t2.companynr = 37; 1307fld1 fld3 period price price2 1308018201 relaxing 1001 5987435 234724 1309018601 vacuuming 1001 5987435 234724 1310018801 inch 1001 5987435 234724 1311018811 repetitions 1001 5987435 234724 1312create table t4 ( 1313companynr tinyint(2) unsigned zerofill NOT NULL default '00', 1314companyname char(30) NOT NULL default '', 1315PRIMARY KEY (companynr), 1316UNIQUE KEY companyname(companyname) 1317) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames'; 1318select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1319companynr companyname 132000 Unknown 132129 company 1 132234 company 2 132336 company 3 132437 company 4 132540 company 5 132641 company 6 132750 company 11 132853 company 7 132958 company 8 133065 company 9 133168 company 10 1332select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1333companynr companyname 133400 Unknown 133529 company 1 133634 company 2 133736 company 3 133837 company 4 133940 company 5 134041 company 6 134150 company 11 134253 company 7 134358 company 8 134465 company 9 134568 company 10 1346select * from t1,t1 t12; 1347Period Varor_period Period Varor_period 13489410 9412 9410 9412 1349select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505; 1350fld1 fld1 1351250501 250501 1352250502 250501 1353250503 250501 1354250504 250501 1355250505 250501 1356250501 250502 1357250502 250502 1358250503 250502 1359250504 250502 1360250505 250502 1361250501 250503 1362250502 250503 1363250503 250503 1364250504 250503 1365250505 250503 1366250501 250504 1367250502 250504 1368250503 250504 1369250504 250504 1370250505 250504 1371250501 250505 1372250502 250505 1373250503 250505 1374250504 250505 1375250505 250505 1376insert into t2 (fld1, companynr) values (999999,99); 1377select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1378companynr companyname 137999 NULL 1380select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null; 1381count(*) 13821199 1383explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1384id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13851 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1200 100.00 NULL 13861 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 10.00 Using where; Not exists; Using join buffer (Block Nested Loop) 1387Warnings: 1388Note 1003 /* select#1 */ select `test`.`t2`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t2` left join `test`.`t4` on((`test`.`t4`.`companynr` = `test`.`t2`.`companynr`)) where isnull(`test`.`t4`.`companynr`) 1389explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; 1390id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13911 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 100.00 NULL 13921 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1200 10.00 Using where; Not exists; Using join buffer (Block Nested Loop) 1393Warnings: 1394Note 1003 /* select#1 */ select `test`.`t2`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` left join `test`.`t2` on((`test`.`t2`.`companynr` = `test`.`t4`.`companynr`)) where isnull(`test`.`t2`.`companynr`) 1395select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1396companynr companyname 1397select count(*) from t2 left join t4 using (companynr) where companynr is not null; 1398count(*) 13991200 1400explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1401id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14021 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1403Warnings: 1404Note 1003 /* select#1 */ select `test`.`t2`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t2` left join `test`.`t4` on(multiple equal(`test`.`t2`.`companynr`, `test`.`t4`.`companynr`)) where 0 1405explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null; 1406id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14071 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1408Warnings: 1409Note 1003 /* select#1 */ select `test`.`t4`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` left join `test`.`t2` on(multiple equal(`test`.`t4`.`companynr`, `test`.`t2`.`companynr`)) where 0 1410delete from t2 where fld1=999999; 1411explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; 1412id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14131 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 33.33 Using where 14141 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 100.00 NULL 1415Warnings: 1416Note 1003 /* select#1 */ select `test`.`t2`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` join `test`.`t2` where ((`test`.`t4`.`companynr` = `test`.`t2`.`companynr`) and (`test`.`t2`.`companynr` > 0)) 1417explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; 1418id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14191 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 55.55 Using where 14201 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 100.00 NULL 1421Warnings: 1422Note 1003 /* select#1 */ select `test`.`t2`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` join `test`.`t2` where ((`test`.`t4`.`companynr` = `test`.`t2`.`companynr`) and ((`test`.`t2`.`companynr` > 0) or (`test`.`t2`.`companynr` < 0))) 1423explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; 1424id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14251 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 33.33 Using where 14261 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 100.00 NULL 1427Warnings: 1428Note 1003 /* select#1 */ select `test`.`t2`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` join `test`.`t2` where ((`test`.`t4`.`companynr` = `test`.`t2`.`companynr`) and (`test`.`t2`.`companynr` > 0) and (`test`.`t2`.`companynr` > 0)) 1429explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; 1430id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14311 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 91.67 Using where 14321 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where; Using join buffer (Block Nested Loop) 1433Warnings: 1434Note 1003 /* select#1 */ select `test`.`t4`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` left join `test`.`t2` on((`test`.`t2`.`companynr` = `test`.`t4`.`companynr`)) where (`test`.`t4`.`companynr` > 0) 1435explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; 1436id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14371 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 100.00 Using where 14381 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where; Using join buffer (Block Nested Loop) 1439Warnings: 1440Note 1003 /* select#1 */ select `test`.`t4`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` left join `test`.`t2` on((`test`.`t2`.`companynr` = `test`.`t4`.`companynr`)) where ((`test`.`t4`.`companynr` > 0) or (`test`.`t4`.`companynr` < 0)) 1441explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; 1442id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14431 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 91.67 Using where 14441 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where; Using join buffer (Block Nested Loop) 1445Warnings: 1446Note 1003 /* select#1 */ select `test`.`t4`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` left join `test`.`t2` on((`test`.`t2`.`companynr` = `test`.`t4`.`companynr`)) where ((`test`.`t4`.`companynr` > 0) and (`test`.`t4`.`companynr` > 0)) 1447explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; 1448id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14491 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 100.00 NULL 14501 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 40.00 Using where; Using join buffer (Block Nested Loop) 1451Warnings: 1452Note 1003 /* select#1 */ select `test`.`t2`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` left join `test`.`t2` on((`test`.`t2`.`companynr` = `test`.`t4`.`companynr`)) where ((`test`.`t2`.`companynr` > 0) or isnull(`test`.`t2`.`companynr`)) 1453explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; 1454id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14551 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 100.00 NULL 14561 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where; Using join buffer (Block Nested Loop) 1457Warnings: 1458Note 1003 /* select#1 */ select `test`.`t2`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` left join `test`.`t2` on((`test`.`t2`.`companynr` = `test`.`t4`.`companynr`)) where ((`test`.`t2`.`companynr` > 0) or (`test`.`t2`.`companynr` < 0) or (`test`.`t4`.`companynr` > 0)) 1459explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; 1460id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14611 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 100.00 NULL 14621 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where; Using join buffer (Block Nested Loop) 1463Warnings: 1464Note 1003 /* select#1 */ select `test`.`t2`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` left join `test`.`t2` on((`test`.`t2`.`companynr` = `test`.`t4`.`companynr`)) where (ifnull(`test`.`t2`.`companynr`,1) > 0) 1465explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; 1466id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14671 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 91.67 Using where 14681 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where; Using join buffer (Block Nested Loop) 1469Warnings: 1470Note 1003 /* select#1 */ select `test`.`t4`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` left join `test`.`t2` on((`test`.`t2`.`companynr` = `test`.`t4`.`companynr`)) where (`test`.`t4`.`companynr` > 0) 1471explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; 1472id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14731 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 100.00 Using where 14741 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where; Using join buffer (Block Nested Loop) 1475Warnings: 1476Note 1003 /* select#1 */ select `test`.`t4`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` left join `test`.`t2` on((`test`.`t2`.`companynr` = `test`.`t4`.`companynr`)) where ((`test`.`t4`.`companynr` > 0) or (`test`.`t4`.`companynr` < 0) or (`test`.`t4`.`companynr` > 0)) 1477explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; 1478id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14791 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 100.00 Using where 14801 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where; Using join buffer (Block Nested Loop) 1481Warnings: 1482Note 1003 /* select#1 */ select `test`.`t4`.`companynr` AS `companynr`,`test`.`t4`.`companyname` AS `companyname` from `test`.`t4` left join `test`.`t2` on((`test`.`t2`.`companynr` = `test`.`t4`.`companynr`)) where (ifnull(`test`.`t4`.`companynr`,1) > 0) 1483select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1484companynr companynr 148537 36 148641 40 1487explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1488id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14891 SIMPLE t4 NULL index NULL PRIMARY 1 NULL 12 100.00 Using index; Using temporary 14901 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 10.00 Using where; Using join buffer (Block Nested Loop) 1491Warnings: 1492Note 1003 /* select#1 */ select distinct `test`.`t2`.`companynr` AS `companynr`,`test`.`t4`.`companynr` AS `companynr` from `test`.`t2` join `test`.`t4` where (`test`.`t2`.`companynr` = (`test`.`t4`.`companynr` + 1)) 1493select 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; 1494fld1 companynr fld3 period 1495038008 37 reporters 1008 1496038208 37 Selfridge 1008 1497select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t2.fld1 = 38208 or t2.fld1 = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009; 1498fld1 companynr fld3 period 1499038008 37 reporters 1008 1500038208 37 Selfridge 1008 1501select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t3.t2nr = 38208 or t3.t2nr = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009; 1502fld1 companynr fld3 period 1503038008 37 reporters 1008 1504038208 37 Selfridge 1008 1505select period from t1 where (((period > 0) or period < 10000 or (period = 1900)) and (period=1900 and period <= 1901) or (period=1903 and (period=1903)) and period>=1902) or ((period=1904 or period=1905) or (period=1906 or period>1907)) or (period=1908 and period = 1909); 1506period 15079410 1508select period from t1 where ((period > 0 and period < 1) or (((period > 0 and period < 100) and (period > 10)) or (period > 10)) or (period > 0 and (period > 5 or period > 6))); 1509period 15109410 1511select a.fld1 from t2 as a,t2 b where ((a.fld1 = 250501 and a.fld1=b.fld1) or a.fld1=250502 or a.fld1=250503 or (a.fld1=250505 and a.fld1<=b.fld1 and b.fld1>=a.fld1)) and a.fld1=b.fld1; 1512fld1 1513250501 1514250502 1515250503 1516250505 1517select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606); 1518fld1 1519250502 1520250503 1521select fld1 from t2 where fld1 between 250502 and 250504; 1522fld1 1523250502 1524250503 1525250504 1526select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ; 1527fld3 1528label 1529labeled 1530labeled 1531landslide 1532laterally 1533leaflet 1534lewdly 1535Lillian 1536luckily 1537select count(*) from t1; 1538count(*) 15391 1540select companynr,count(*),sum(fld1) from t2 group by companynr; 1541companynr count(*) sum(fld1) 154200 82 10355753 154329 95 14473298 154434 70 17788966 154536 215 22786296 154637 588 83602098 154740 37 6618386 154841 52 12816335 154950 11 1595438 155053 4 793210 155158 23 2254293 155265 10 2284055 155368 12 3097288 1554select companynr,count(*) from t2 group by companynr order by companynr desc limit 5; 1555companynr count(*) 155668 12 155765 10 155858 23 155953 4 156050 11 1561select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1562count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 156370 absentee vest 17788966 254128.0857 3272.5939722090234 10709871.306938833 1564explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1565id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15661 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 9.00 Using where 1567Warnings: 1568Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1569Note 1003 /* select#1 */ 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` <> '')) 1570select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; 1571companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 157200 82 Anthony windmills 10355753 126289.6707 115550.97568479746 13352027981.708656 157329 95 abut wetness 14473298 152350.5053 8368.547956641249 70032594.90260443 157434 70 absentee vest 17788966 254128.0857 3272.5939722090234 10709871.306938833 1575select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10; 1576companynr t2nr count(price) sum(price) min(price) max(price) avg(price) 157737 1 1 5987435 5987435 5987435 5987435.0000 157837 2 1 28357832 28357832 28357832 28357832.0000 157937 3 1 39654943 39654943 39654943 39654943.0000 158037 11 1 5987435 5987435 5987435 5987435.0000 158137 12 1 28357832 28357832 28357832 28357832.0000 158237 13 1 39654943 39654943 39654943 39654943.0000 158337 21 1 5987435 5987435 5987435 5987435.0000 158437 22 1 28357832 28357832 28357832 28357832.0000 158537 23 1 39654943 39654943 39654943 39654943.0000 158637 31 1 5987435 5987435 5987435 5987435.0000 1587select /*! 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; 1588companynr t2nr count(price) sum(price) min(price) max(price) avg(price) 158937 1 1 5987435 5987435 5987435 5987435.0000 159037 2 1 28357832 28357832 28357832 28357832.0000 159137 3 1 39654943 39654943 39654943 39654943.0000 159237 11 1 5987435 5987435 5987435 5987435.0000 159337 12 1 28357832 28357832 28357832 28357832.0000 159437 13 1 39654943 39654943 39654943 39654943.0000 159537 21 1 5987435 5987435 5987435 5987435.0000 159637 22 1 28357832 28357832 28357832 28357832.0000 159737 23 1 39654943 39654943 39654943 39654943.0000 159837 31 1 5987435 5987435 5987435 5987435.0000 1599select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ; 1600companynr count(price) sum(price) min(price) max(price) avg(price) 160137 12543 309394878010 5987435 39654943 24666736.6667 160278 8362 414611089292 726498 98439034 49582766.0000 1603101 4181 3489454238 834598 834598 834598.0000 1604154 4181 4112197254950 983543950 983543950 983543950.0000 1605311 4181 979599938 234298 234298 234298.0000 1606447 4181 9929180954 2374834 2374834 2374834.0000 1607512 4181 3288532102 786542 786542 786542.0000 1608select distinct mod(companynr,10) from t4 group by companynr; 1609mod(companynr,10) 16100 16119 16124 16136 16147 16151 16163 16178 16185 1619select distinct 1 from t4 group by companynr; 16201 16211 1622select count(distinct fld1) from t2; 1623count(distinct fld1) 16241199 1625select companynr,count(distinct fld1) from t2 group by companynr; 1626companynr count(distinct fld1) 162700 82 162829 95 162934 70 163036 215 163137 588 163240 37 163341 52 163450 11 163553 4 163658 23 163765 10 163868 12 1639select companynr,count(*) from t2 group by companynr; 1640companynr count(*) 164100 82 164229 95 164334 70 164436 215 164537 588 164640 37 164741 52 164850 11 164953 4 165058 23 165165 10 165268 12 1653select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr; 1654companynr count(distinct concat(fld1,repeat(65,1000))) 165500 82 165629 95 165734 70 165836 215 165937 588 166040 37 166141 52 166250 11 166353 4 166458 23 166565 10 166668 12 1667select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr; 1668companynr count(distinct concat(fld1,repeat(65,200))) 166900 82 167029 95 167134 70 167236 215 167337 588 167440 37 167541 52 167650 11 167753 4 167858 23 167965 10 168068 12 1681select companynr,count(distinct floor(fld1/100)) from t2 group by companynr; 1682companynr count(distinct floor(fld1/100)) 168300 47 168429 35 168534 14 168636 69 168737 108 168840 16 168941 11 169050 9 169153 1 169258 1 169365 1 169468 1 1695select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr; 1696companynr count(distinct concat(repeat(65,1000),floor(fld1/100))) 169700 47 169829 35 169934 14 170036 69 170137 108 170240 16 170341 11 170450 9 170553 1 170658 1 170765 1 170868 1 1709select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10; 1710sum(fld1) fld3 171111402 Romans 1712select name,count(*) from t3 where name='cloakroom' group by name; 1713name count(*) 1714cloakroom 4181 1715select name,count(*) from t3 where name='cloakroom' and price>10 group by name; 1716name count(*) 1717cloakroom 4181 1718select count(*) from t3 where name='cloakroom' and price2=823742; 1719count(*) 17204181 1721select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name; 1722name count(*) 1723cloakroom 4181 1724select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name; 1725name count(*) 1726extramarital 4181 1727gazer 4181 1728gems 4181 1729Iranizes 4181 1730spates 4181 1731tucked 4181 1732violinist 4181 1733select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1734fld3 count(*) 1735spates 4181 1736select companynr|0,companyname from t4 group by 1; 1737companynr|0 companyname 17380 Unknown 173929 company 1 174034 company 2 174136 company 3 174237 company 4 174340 company 5 174441 company 6 174550 company 11 174653 company 7 174758 company 8 174865 company 9 174968 company 10 1750select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname; 1751companynr companyname count(*) 175229 company 1 95 175368 company 10 12 175450 company 11 11 175534 company 2 70 175636 company 3 215 175737 company 4 588 175840 company 5 37 175941 company 6 52 176053 company 7 4 176158 company 8 23 176265 company 9 10 176300 Unknown 82 1764select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1765fld1 count(*) 1766158402 4181 1767select sum(Period)/count(*) from t1; 1768sum(Period)/count(*) 17699410.0000 1770select companynr,count(price) as "count",sum(price) as "sum" ,abs(sum(price)/count(price)-avg(price)) as "diff",(0+count(price))*companynr as func from t3 group by companynr; 1771companynr count sum diff func 177237 12543 309394878010 0.0000 464091 177378 8362 414611089292 0.0000 652236 1774101 4181 3489454238 0.0000 422281 1775154 4181 4112197254950 0.0000 643874 1776311 4181 979599938 0.0000 1300291 1777447 4181 9929180954 0.0000 1868907 1778512 4181 3288532102 0.0000 2140672 1779select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg; 1780companynr avg 1781154 983543950.0000 1782select companynr,count(*) from t2 group by companynr order by 2 desc; 1783companynr count(*) 178437 588 178536 215 178629 95 178700 82 178834 70 178941 52 179040 37 179158 23 179268 12 179350 11 179465 10 179553 4 1796select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc; 1797companynr count(*) 179841 52 179958 23 180068 12 180150 11 180265 10 180353 4 1804select t2.fld4,t2.fld1,count(price),sum(price),min(price),max(price),avg(price) from t3,t2 where t3.companynr = 37 and t2.fld1 = t3.t2nr group by fld1,t2.fld4; 1805fld4 fld1 count(price) sum(price) min(price) max(price) avg(price) 1806teethe 000001 1 5987435 5987435 5987435 5987435.0000 1807dreaded 011401 1 5987435 5987435 5987435 5987435.0000 1808scholastics 011402 1 28357832 28357832 28357832 28357832.0000 1809audiology 011403 1 39654943 39654943 39654943 39654943.0000 1810wallet 011501 1 5987435 5987435 5987435 5987435.0000 1811parters 011701 1 5987435 5987435 5987435 5987435.0000 1812eschew 011702 1 28357832 28357832 28357832 28357832.0000 1813quitter 011703 1 39654943 39654943 39654943 39654943.0000 1814neat 012001 1 5987435 5987435 5987435 5987435.0000 1815Steinberg 012003 1 39654943 39654943 39654943 39654943.0000 1816balled 012301 1 5987435 5987435 5987435 5987435.0000 1817persist 012302 1 28357832 28357832 28357832 28357832.0000 1818attainments 012303 1 39654943 39654943 39654943 39654943.0000 1819capably 012501 1 5987435 5987435 5987435 5987435.0000 1820impulsive 012602 1 28357832 28357832 28357832 28357832.0000 1821starlet 012603 1 39654943 39654943 39654943 39654943.0000 1822featherweight 012701 1 5987435 5987435 5987435 5987435.0000 1823pessimist 012702 1 28357832 28357832 28357832 28357832.0000 1824daughter 012703 1 39654943 39654943 39654943 39654943.0000 1825lawgiver 013601 1 5987435 5987435 5987435 5987435.0000 1826stated 013602 1 28357832 28357832 28357832 28357832.0000 1827readable 013603 1 39654943 39654943 39654943 39654943.0000 1828testicle 013801 1 5987435 5987435 5987435 5987435.0000 1829Parsifal 013802 1 28357832 28357832 28357832 28357832.0000 1830leavings 013803 1 39654943 39654943 39654943 39654943.0000 1831squeaking 013901 1 5987435 5987435 5987435 5987435.0000 1832contrasted 016001 1 5987435 5987435 5987435 5987435.0000 1833leftover 016201 1 5987435 5987435 5987435 5987435.0000 1834whiteners 016202 1 28357832 28357832 28357832 28357832.0000 1835erases 016301 1 5987435 5987435 5987435 5987435.0000 1836Punjab 016302 1 28357832 28357832 28357832 28357832.0000 1837Merritt 016303 1 39654943 39654943 39654943 39654943.0000 1838sweetish 018001 1 5987435 5987435 5987435 5987435.0000 1839dogging 018002 1 28357832 28357832 28357832 28357832.0000 1840scornfully 018003 1 39654943 39654943 39654943 39654943.0000 1841fetters 018012 1 28357832 28357832 28357832 28357832.0000 1842bivalves 018013 1 39654943 39654943 39654943 39654943.0000 1843skulking 018021 1 5987435 5987435 5987435 5987435.0000 1844flint 018022 1 28357832 28357832 28357832 28357832.0000 1845flopping 018023 1 39654943 39654943 39654943 39654943.0000 1846Judas 018032 1 28357832 28357832 28357832 28357832.0000 1847vacuuming 018033 1 39654943 39654943 39654943 39654943.0000 1848medical 018041 1 5987435 5987435 5987435 5987435.0000 1849bloodbath 018042 1 28357832 28357832 28357832 28357832.0000 1850subschema 018043 1 39654943 39654943 39654943 39654943.0000 1851interdependent 018051 1 5987435 5987435 5987435 5987435.0000 1852Graves 018052 1 28357832 28357832 28357832 28357832.0000 1853neonatal 018053 1 39654943 39654943 39654943 39654943.0000 1854sorters 018061 1 5987435 5987435 5987435 5987435.0000 1855epistle 018062 1 28357832 28357832 28357832 28357832.0000 1856Conley 018101 1 5987435 5987435 5987435 5987435.0000 1857lectured 018102 1 28357832 28357832 28357832 28357832.0000 1858Abraham 018103 1 39654943 39654943 39654943 39654943.0000 1859cage 018201 1 5987435 5987435 5987435 5987435.0000 1860hushes 018202 1 28357832 28357832 28357832 28357832.0000 1861Simla 018402 1 28357832 28357832 28357832 28357832.0000 1862reporters 018403 1 39654943 39654943 39654943 39654943.0000 1863coexist 018601 1 5987435 5987435 5987435 5987435.0000 1864Beebe 018602 1 28357832 28357832 28357832 28357832.0000 1865Taoism 018603 1 39654943 39654943 39654943 39654943.0000 1866Connally 018801 1 5987435 5987435 5987435 5987435.0000 1867fetched 018802 1 28357832 28357832 28357832 28357832.0000 1868checkpoints 018803 1 39654943 39654943 39654943 39654943.0000 1869gritty 018811 1 5987435 5987435 5987435 5987435.0000 1870firearm 018812 1 28357832 28357832 28357832 28357832.0000 1871minima 019101 1 5987435 5987435 5987435 5987435.0000 1872Selfridge 019102 1 28357832 28357832 28357832 28357832.0000 1873disable 019103 1 39654943 39654943 39654943 39654943.0000 1874witchcraft 019201 1 5987435 5987435 5987435 5987435.0000 1875betroth 030501 1 5987435 5987435 5987435 5987435.0000 1876Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000 1877imprint 030503 1 39654943 39654943 39654943 39654943.0000 1878swelling 031901 1 5987435 5987435 5987435 5987435.0000 1879interrelationships 036001 1 5987435 5987435 5987435 5987435.0000 1880riser 036002 1 28357832 28357832 28357832 28357832.0000 1881bee 038001 1 5987435 5987435 5987435 5987435.0000 1882kanji 038002 1 28357832 28357832 28357832 28357832.0000 1883dental 038003 1 39654943 39654943 39654943 39654943.0000 1884railway 038011 1 5987435 5987435 5987435 5987435.0000 1885validate 038012 1 28357832 28357832 28357832 28357832.0000 1886normalizes 038013 1 39654943 39654943 39654943 39654943.0000 1887Kline 038101 1 5987435 5987435 5987435 5987435.0000 1888Anatole 038102 1 28357832 28357832 28357832 28357832.0000 1889partridges 038103 1 39654943 39654943 39654943 39654943.0000 1890recruited 038201 1 5987435 5987435 5987435 5987435.0000 1891dimensions 038202 1 28357832 28357832 28357832 28357832.0000 1892Chicana 038203 1 39654943 39654943 39654943 39654943.0000 1893select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3; 1894companynr fld3 sum(price) 1895512 boat 786542 1896512 capably 786542 1897512 cupboard 786542 1898512 decliner 786542 1899512 descendants 786542 1900512 dopers 786542 1901512 erases 786542 1902512 Micronesia 786542 1903512 Miles 786542 1904512 skies 786542 1905select t2.companynr,count(*),min(fld3),max(fld3),sum(price),avg(price) from t2,t3 where t3.companynr >= 30 and t3.companynr <= 58 and t3.t2nr = t2.fld1 and 1+1=2 group by t2.companynr; 1906companynr count(*) min(fld3) max(fld3) sum(price) avg(price) 190700 1 Omaha Omaha 5987435 5987435.0000 190836 1 dubbed dubbed 28357832 28357832.0000 190937 83 Abraham Wotan 1908978016 22999735.1325 191050 2 scribbled tapestry 68012775 34006387.5000 1911select t3.companynr+0,t3.t2nr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 37 group by 1,t3.t2nr,fld3,fld3,fld3,fld3,fld3 order by fld1; 1912t3.companynr+0 t2nr fld3 sum(price) 191337 1 Omaha 5987435 191437 11401 breaking 5987435 191537 11402 Romans 28357832 191637 11403 intercepted 39654943 191737 11501 bewilderingly 5987435 191837 11701 astound 5987435 191937 11702 admonishing 28357832 192037 11703 sumac 39654943 192137 12001 flanking 5987435 192237 12003 combed 39654943 192337 12301 Eulerian 5987435 192437 12302 dubbed 28357832 192537 12303 Kane 39654943 192637 12501 annihilates 5987435 192737 12602 Wotan 28357832 192837 12603 snatching 39654943 192937 12701 grazing 5987435 193037 12702 Baird 28357832 193137 12703 celery 39654943 193237 13601 handgun 5987435 193337 13602 foldout 28357832 193437 13603 mystic 39654943 193537 13801 intelligibility 5987435 193637 13802 Augustine 28357832 193737 13803 teethe 39654943 193837 13901 scholastics 5987435 193937 16001 audiology 5987435 194037 16201 wallet 5987435 194137 16202 parters 28357832 194237 16301 eschew 5987435 194337 16302 quitter 28357832 194437 16303 neat 39654943 194537 18001 jarring 5987435 194637 18002 tinily 28357832 194737 18003 balled 39654943 194837 18012 impulsive 28357832 194937 18013 starlet 39654943 195037 18021 lawgiver 5987435 195137 18022 stated 28357832 195237 18023 readable 39654943 195337 18032 testicle 28357832 195437 18033 Parsifal 39654943 195537 18041 Punjab 5987435 195637 18042 Merritt 28357832 195737 18043 Quixotism 39654943 195837 18051 sureties 5987435 195937 18052 puddings 28357832 196037 18053 tapestry 39654943 196137 18061 trimmings 5987435 196237 18062 humility 28357832 196337 18101 tragedies 5987435 196437 18102 skulking 28357832 196537 18103 flint 39654943 196637 18201 relaxing 5987435 196737 18202 offload 28357832 196837 18402 suites 28357832 196937 18403 lists 39654943 197037 18601 vacuuming 5987435 197137 18602 dentally 28357832 197237 18603 humanness 39654943 197337 18801 inch 5987435 197437 18802 Weissmuller 28357832 197537 18803 irresponsibly 39654943 197637 18811 repetitions 5987435 197737 18812 Antares 28357832 197837 19101 ventilate 5987435 197937 19102 pityingly 28357832 198037 19103 interdependent 39654943 198137 19201 Graves 5987435 198237 30501 neonatal 5987435 198337 30502 scribbled 28357832 198437 30503 chafe 39654943 198537 31901 realtor 5987435 198637 36001 elite 5987435 198737 36002 funereal 28357832 198837 38001 Conley 5987435 198937 38002 lectured 28357832 199037 38003 Abraham 39654943 199137 38011 groupings 5987435 199237 38012 dissociate 28357832 199337 38013 coexist 39654943 199437 38101 rusting 5987435 199537 38102 galling 28357832 199637 38103 obliterates 39654943 199737 38201 resumes 5987435 199837 38202 analyzable 28357832 199937 38203 terminator 39654943 2000select 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; 2001sum(price) 2002234298 2003select 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; 2004fld1 sum(price) 2005038008 234298 2006explain select fld3 from t2 where 1>2 or 2>3; 2007id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20081 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2009Warnings: 2010Note 1003 /* select#1 */ select `test`.`t2`.`fld3` AS `fld3` from `test`.`t2` where 0 2011explain select fld3 from t2 where fld1=fld1; 2012id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20131 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 NULL 2014Warnings: 2015Note 1003 /* select#1 */ select `test`.`t2`.`fld3` AS `fld3` from `test`.`t2` where 1 2016select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502; 2017companynr fld1 201834 250501 201934 250502 2020select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502; 2021companynr fld1 202234 250501 202334 250502 2024select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000; 2025companynr count sum 202600 82 10355753 202729 95 14473298 202834 70 17788966 202937 588 83602098 203041 52 12816335 2031select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ; 2032companynr 203300 203429 203534 203637 203741 2038select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40; 2039companynr companyname count(*) 204068 company 10 12 204150 company 11 11 204240 company 5 37 204341 company 6 52 204453 company 7 4 204558 company 8 23 204665 company 9 10 2047select count(*) from t2; 2048count(*) 20491199 2050select count(*) from t2 where fld1 < 098024; 2051count(*) 2052387 2053select min(fld1) from t2 where fld1>= 098024; 2054min(fld1) 205598024 2056select max(fld1) from t2 where fld1>= 098024; 2057max(fld1) 20581232609 2059select count(*) from t3 where price2=76234234; 2060count(*) 20614181 2062select count(*) from t3 where companynr=512 and price2=76234234; 2063count(*) 20644181 2065explain select min(fld1),max(fld1),count(*) from t2; 2066id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20671 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2068Warnings: 2069Note 1003 /* select#1 */ select min(`test`.`t2`.`fld1`) AS `min(fld1)`,max(`test`.`t2`.`fld1`) AS `max(fld1)`,count(0) AS `count(*)` from `test`.`t2` 2070select min(fld1),max(fld1),count(*) from t2; 2071min(fld1) max(fld1) count(*) 20720 1232609 1199 2073select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742; 2074min(t2nr) max(t2nr) 20752115 2115 2076select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78; 2077count(*) min(t2nr) max(t2nr) 20784181 4 41804 2079select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20; 2080t2nr count(*) 20819 1 208219 1 208329 1 208439 1 208549 1 208659 1 208769 1 208879 1 208989 1 209099 1 2091109 1 2092119 1 2093129 1 2094139 1 2095149 1 2096159 1 2097169 1 2098179 1 2099189 1 2100199 1 2101select max(t2nr) from t3 where price=983543950; 2102max(t2nr) 210341807 2104select t1.period from t3 = t1 limit 1; 2105period 21061001 2107select t1.period from t1 as t1 limit 1; 2108period 21099410 2110select t1.period as "Nuvarande period" from t1 as t1 limit 1; 2111Nuvarande period 21129410 2113select period as ok_period from t1 limit 1; 2114ok_period 21159410 2116select period as ok_period from t1 group by ok_period limit 1; 2117ok_period 21189410 2119select 1+1 as summa from t1 group by summa limit 1; 2120summa 21212 2122select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1; 2123Nuvarande period 21249410 2125show tables; 2126Tables_in_test 2127t1 2128t2 2129t3 2130t4 2131show tables from test like "s%"; 2132Tables_in_test (s%) 2133show tables from test like "t?"; 2134Tables_in_test (t?) 2135show full columns from t2; 2136Field Type Collation Null Key Default Extra Privileges Comment 2137auto int(11) NULL NO PRI NULL auto_increment # 2138fld1 int(6) unsigned zerofill NULL NO UNI 000000 # 2139companynr tinyint(2) unsigned zerofill NULL NO 00 # 2140fld3 char(30) latin1_swedish_ci NO MUL # 2141fld4 char(35) latin1_swedish_ci NO # 2142fld5 char(35) latin1_swedish_ci NO # 2143fld6 char(4) latin1_swedish_ci NO # 2144show full columns from t2 from test like 'f%'; 2145Field Type Collation Null Key Default Extra Privileges Comment 2146fld1 int(6) unsigned zerofill NULL NO UNI 000000 # 2147fld3 char(30) latin1_swedish_ci NO MUL # 2148fld4 char(35) latin1_swedish_ci NO # 2149fld5 char(35) latin1_swedish_ci NO # 2150fld6 char(4) latin1_swedish_ci NO # 2151show full columns from t2 from test like 's%'; 2152Field Type Collation Null Key Default Extra Privileges Comment 2153show keys from t2; 2154Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2155t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE 2156t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE 2157t2 1 fld3 1 fld3 A NULL NULL NULL BTREE 2158drop table t4, t3, t2, t1; 2159SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 2160Warnings: 2161Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 2162CREATE TABLE t1 ( 2163cont_nr int(11) NOT NULL auto_increment, 2164ver_nr int(11) NOT NULL default '0', 2165aufnr int(11) NOT NULL default '0', 2166username varchar(50) NOT NULL default '', 2167hdl_nr int(11) NOT NULL default '0', 2168eintrag date NOT NULL default '0000-00-00', 2169st_klasse varchar(40) NOT NULL default '', 2170st_wert varchar(40) NOT NULL default '', 2171st_zusatz varchar(40) NOT NULL default '', 2172st_bemerkung varchar(255) NOT NULL default '', 2173kunden_art varchar(40) NOT NULL default '', 2174mcbs_knr int(11) default NULL, 2175mcbs_aufnr int(11) NOT NULL default '0', 2176schufa_status char(1) default '?', 2177bemerkung text, 2178wirknetz text, 2179wf_igz int(11) NOT NULL default '0', 2180tarifcode varchar(80) default NULL, 2181recycle char(1) default NULL, 2182sim varchar(30) default NULL, 2183mcbs_tpl varchar(30) default NULL, 2184emp_nr int(11) NOT NULL default '0', 2185laufzeit int(11) default NULL, 2186hdl_name varchar(30) default NULL, 2187prov_hdl_nr int(11) NOT NULL default '0', 2188auto_wirknetz varchar(50) default NULL, 2189auto_billing varchar(50) default NULL, 2190touch timestamp NOT NULL, 2191kategorie varchar(50) default NULL, 2192kundentyp varchar(20) NOT NULL default '', 2193sammel_rech_msisdn varchar(30) NOT NULL default '', 2194p_nr varchar(9) NOT NULL default '', 2195suffix char(3) NOT NULL default '', 2196PRIMARY KEY (cont_nr), 2197KEY idx_aufnr(aufnr), 2198KEY idx_hdl_nr(hdl_nr), 2199KEY idx_st_klasse(st_klasse), 2200KEY ver_nr(ver_nr), 2201KEY eintrag_idx(eintrag), 2202KEY emp_nr_idx(emp_nr), 2203KEY wf_igz(wf_igz), 2204KEY touch(touch), 2205KEY hdl_tag(eintrag,hdl_nr), 2206KEY prov_hdl_nr(prov_hdl_nr), 2207KEY mcbs_aufnr(mcbs_aufnr), 2208KEY kundentyp(kundentyp), 2209KEY p_nr(p_nr,suffix) 2210) ENGINE=MyISAM; 2211SET sql_mode = default; 2212INSERT INTO t1 VALUES (3359356,405,3359356,'Mustermann Musterfrau',52500,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1485525,2122316,'+','','N',1909160,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',3,24,'MobilCom Shop Koeln',52500,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); 2213INSERT INTO t1 VALUES (3359357,468,3359357,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1503580,2139699,'+','','P',1909171,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); 2214INSERT INTO t1 VALUES (3359358,407,3359358,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1501358,2137473,'N','','N',1909159,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); 2215INSERT INTO t1 VALUES (3359359,468,3359359,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1507831,2143894,'+','','P',1909162,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); 2216INSERT INTO t1 VALUES (3359360,0,0,'Mustermann Musterfrau',29674907,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1900169997,2414578,'+',NULL,'N',1909148,'',NULL,NULL,'RV99066_2',20,NULL,'POS',29674907,NULL,NULL,20010202105916,'Mobilfunk','','','97317481','007'); 2217INSERT INTO t1 VALUES (3359361,406,3359361,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag storniert','','(7001-84):Storno, Kd. möchte nicht mehr','privat',NULL,0,'+','','P',1909150,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); 2218INSERT INTO t1 VALUES (3359362,406,3359362,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1509984,2145874,'+','','P',1909154,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); 2219SELECT ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA','V','VA',''), 'Privat (Private Nutzung)','Privat (Private Nutzung) Sitz im Ausland','Privat (geschaeftliche Nutzung)','Privat (geschaeftliche Nutzung) Sitz im Ausland','Firma (Kapitalgesellschaft)','Firma (Kapitalgesellschaft) Sitz im Ausland','Firma (Personengesellschaft)','Firma (Personengesellschaft) Sitz im Ausland','oeff. rechtl. Koerperschaft','oeff. rechtl. Koerperschaft Sitz im Ausland','Eingetragener Verein','Eingetragener Verein Sitz im Ausland','Typ unbekannt') AS Kundentyp ,kategorie FROM t1 WHERE hdl_nr < 2000000 AND kategorie IN ('Prepaid','Mobilfunk') AND st_klasse = 'Workflow' GROUP BY kundentyp ORDER BY kategorie; 2220Kundentyp kategorie 2221Privat (Private Nutzung) Mobilfunk 2222Warnings: 2223Warning 1052 Column 'kundentyp' in group statement is ambiguous 2224drop table t1; 2225