1set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 2set long_query_time=0.1; 3TEST GROUP 1: 4Typical cases of in-to-exists and materialization subquery strategies 5===================================================================== 6drop database if exists world; 7set names utf8; 8create database world; 9use world; 10CREATE TABLE Country ( 11Code char(3) NOT NULL default '', 12Name char(52) NOT NULL default '', 13SurfaceArea float(10,2) NOT NULL default '0.00', 14Population int(11) NOT NULL default '0', 15Capital int(11) default NULL, 16PRIMARY KEY (Code), 17UNIQUE INDEX (Name) 18); 19CREATE TABLE City ( 20ID int(11) NOT NULL auto_increment, 21Name char(35) NOT NULL default '', 22Country char(3) NOT NULL default '', 23Population int(11) NOT NULL default '0', 24PRIMARY KEY (ID), 25INDEX (Population), 26INDEX (Country) 27); 28CREATE TABLE CountryLanguage ( 29Country char(3) NOT NULL default '', 30Language char(30) NOT NULL default '', 31Percentage float(3,1) NOT NULL default '0.0', 32PRIMARY KEY (Country, Language), 33INDEX (Percentage) 34); 35Make the schema and data more diverse by adding more indexes, nullable 36columns, and NULL data. 37create index SurfaceArea on Country(SurfaceArea); 38create index Language on CountryLanguage(Language); 39create index CityName on City(Name); 40alter table City change population population int(11) null default 0; 41select max(id) from City into @max_city_id; 42Warnings: 43Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 44insert into City values (@max_city_id + 1,'Kilifarevo','BGR',NULL); 45SELECT COUNT(*) FROM Country; 46COUNT(*) 47239 48SELECT COUNT(*) FROM City; 49COUNT(*) 504080 51SELECT COUNT(*) FROM CountryLanguage; 52COUNT(*) 53984 54set @@optimizer_switch = 'in_to_exists=on,semijoin=on,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on'; 55 561. Subquery in a disjunctive WHERE clause of the outer query. 57 58 59Q1.1m: 60MATERIALIZATION: there are too many rows in the outer query 61to be looked up in the inner table. 62EXPLAIN 63SELECT Name FROM Country 64WHERE (Code IN (select Country from City where City.Population > 100000) OR 65Name LIKE 'L%') AND 66surfacearea > 1000000; 67id select_type table type possible_keys key key_len ref rows Extra 681 PRIMARY Country ALL Name,SurfaceArea NULL NULL NULL 239 Using where 692 MATERIALIZED City ALL Population,Country NULL NULL NULL 4079 Using where 70SELECT Name FROM Country 71WHERE (Code IN (select Country from City where City.Population > 100000) OR 72Name LIKE 'L%') AND 73surfacearea > 1000000; 74Name 75Algeria 76Angola 77Argentina 78Australia 79Bolivia 80Brazil 81Egypt 82South Africa 83Ethiopia 84Indonesia 85India 86Iran 87Canada 88Kazakstan 89China 90Colombia 91Congo, The Democratic Republic of the 92Libyan Arab Jamahiriya 93Mali 94Mauritania 95Mexico 96Mongolia 97Niger 98Peru 99Saudi Arabia 100Sudan 101Chad 102Russian Federation 103United States 104Q1.1e: 105IN-EXISTS: the materialization cost is the same as above, but 106there are much fewer outer rows to be looked up, thus the 107materialization cost is too high to compensate for fast lookups. 108EXPLAIN 109SELECT Name FROM Country 110WHERE (Code IN (select Country from City where City.Population > 100000) OR 111Name LIKE 'L%') AND 112surfacearea > 10*1000000; 113id select_type table type possible_keys key key_len ref rows Extra 1141 PRIMARY Country range Name,SurfaceArea SurfaceArea 4 NULL 5 Using index condition; Using where; Rowid-ordered scan 1152 DEPENDENT SUBQUERY City index_subquery Population,Country Country 3 func 17 Using where 116SELECT Name FROM Country 117WHERE (Code IN (select Country from City where City.Population > 100000) OR 118Name LIKE 'L%') AND 119surfacearea > 10*1000000; 120Name 121Russian Federation 122 123Q1.2m: 124MATERIALIZATION: the IN predicate is pushed (attached) to the last table 125in the join order (Country, City), therefore there are too many row 126combinations to filter by re-executing the subquery for each combination. 127EXPLAIN 128SELECT * 129FROM Country, City 130WHERE City.Country = Country.Code AND 131Country.SurfaceArea < 3000 AND Country.SurfaceArea > 10 AND 132(City.Name IN 133(select Language from CountryLanguage where Percentage > 50) OR 134City.name LIKE '%Island%'); 135id select_type table type possible_keys key key_len ref rows Extra 1361 PRIMARY Country ALL PRIMARY,SurfaceArea NULL NULL NULL 239 Using where 1371 PRIMARY City ref Country Country 3 world.Country.Code 17 Using where 1382 MATERIALIZED CountryLanguage ALL Percentage,Language NULL NULL NULL 984 Using where 139SELECT * 140FROM Country, City 141WHERE City.Country = Country.Code AND 142Country.SurfaceArea < 3000 AND Country.SurfaceArea > 10 AND 143(City.Name IN 144(select Language from CountryLanguage where Percentage > 50) OR 145City.name LIKE '%Island%'); 146Code Name SurfaceArea Population Capital ID Name Country population 147CCK Cocos (Keeling) Islands 14.00 600 2317 2317 West Island CCK 167 148Q1.2e: 149IN_EXISTS: join order is the same, but the left IN operand refers to 150only the first table in the join order (Country), so there are much 151fewer rows to filter by subquery re-execution. 152EXPLAIN 153SELECT * 154FROM Country, City 155WHERE City.Country = Country.Code AND 156Country.SurfaceArea < 3000 AND Country.SurfaceArea > 10 AND 157(Country.Name IN 158(select Language from CountryLanguage where Percentage > 50) OR 159Country.name LIKE '%Island%'); 160id select_type table type possible_keys key key_len ref rows Extra 1611 PRIMARY Country ALL PRIMARY,SurfaceArea NULL NULL NULL 239 Using where 1621 PRIMARY City ref Country Country 3 world.Country.Code 17 1632 DEPENDENT SUBQUERY CountryLanguage index_subquery Percentage,Language Language 30 func 2 Using where 164SELECT * 165FROM Country, City 166WHERE City.Country = Country.Code AND 167Country.SurfaceArea < 3000 AND Country.SurfaceArea > 10 AND 168(Country.Name IN 169(select Language from CountryLanguage where Percentage > 50) OR 170Country.name LIKE '%Island%'); 171Code Name SurfaceArea Population Capital ID Name Country population 172VGB Virgin Islands, British 151.00 21000 537 537 Road Town VGB 8000 173CYM Cayman Islands 264.00 38000 553 553 George Town CYM 19600 174COK Cook Islands 236.00 20000 583 583 Avarua COK 11900 175FRO Faroe Islands 1399.00 43000 901 901 Tórshavn FRO 14542 176CXR Christmas Island 135.00 2500 1791 1791 Flying Fish Cove CXR 700 177KIR Kiribati 726.00 83000 2256 2255 Bikenibeu KIR 5055 178KIR Kiribati 726.00 83000 2256 2256 Bairiki KIR 2226 179CCK Cocos (Keeling) Islands 14.00 600 2317 2316 Bantam CCK 503 180CCK Cocos (Keeling) Islands 14.00 600 2317 2317 West Island CCK 167 181MHL Marshall Islands 181.00 64000 2507 2507 Dalap-Uliga-Darrit MHL 28000 182NRU Nauru 21.00 12000 2728 2727 Yangor NRU 4050 183NRU Nauru 21.00 12000 2728 2728 Yaren NRU 559 184NFK Norfolk Island 36.00 2000 2806 2806 Kingston NFK 800 185PLW Palau 459.00 19000 2881 2881 Koror PLW 12000 186MNP Northern Mariana Islands 464.00 78000 2913 2913 Garapan MNP 9200 187TCA Turks and Caicos Islands 430.00 17000 3423 3423 Cockburn Town TCA 4800 188TUV Tuvalu 26.00 12000 3424 3424 Funafuti TUV 4600 189VIR Virgin Islands, U.S. 347.00 93000 4067 4067 Charlotte Amalie VIR 13000 190 191Q1.3: 192For the same reasons as in Q2 IN-EXISTS and MATERIALIZATION chosen 193for each respective subquery. 194EXPLAIN 195SELECT City.Name, Country.Name 196FROM City,Country 197WHERE City.Country = Country.Code AND 198Country.SurfaceArea < 30000 AND Country.SurfaceArea > 10 AND 199((Country.Code, Country.Name) IN 200(select Country, Language from CountryLanguage where Percentage > 50) AND 201Country.Population > 3000000 202OR 203(Country.Code, City.Name) IN 204(select Country, Language from CountryLanguage)); 205id select_type table type possible_keys key key_len ref rows Extra 2061 PRIMARY Country ALL PRIMARY,SurfaceArea NULL NULL NULL 239 Using where 2071 PRIMARY City ref Country Country 3 world.Country.Code 17 Using where 2083 MATERIALIZED CountryLanguage index PRIMARY,Language PRIMARY 33 NULL 984 Using index 2092 DEPENDENT SUBQUERY CountryLanguage unique_subquery PRIMARY,Percentage,Language PRIMARY 33 func,func 1 Using where 210SELECT City.Name, Country.Name 211FROM City,Country 212WHERE City.Country = Country.Code AND 213Country.SurfaceArea < 30000 AND Country.SurfaceArea > 10 AND 214((Country.Code, Country.Name) IN 215(select Country, Language from CountryLanguage where Percentage > 50) AND 216Country.Population > 3000000 217OR 218(Country.Code, City.Name) IN 219(select Country, Language from CountryLanguage)); 220Name Name 221Kigali Rwanda 222 2232. NOT IN subqueries 224 225 226Q2.1: 227Number of cities that are not capitals in countries with small population. 228MATERIALIZATION is 50 times faster because the cost of each subquery 229re-execution is much higher than the cost of index lookups into the 230materialized subquery. 231EXPLAIN 232select count(*) from City 233where City.id not in (select capital from Country 234where capital is not null and population < 100000); 235id select_type table type possible_keys key key_len ref rows Extra 2361 PRIMARY City index NULL PRIMARY 4 NULL 4079 Using where; Using index 2372 MATERIALIZED Country ALL NULL NULL NULL NULL 239 Using where 238 239Q2.2e: 240Countries that speak French, but do not speak English 241IN-EXISTS because the outer query filters many rows, thus 242there are few lookups to make. 243EXPLAIN 244SELECT Country.Name 245FROM Country, CountryLanguage 246WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English') 247AND CountryLanguage.Language = 'French' 248 AND Code = Country; 249id select_type table type possible_keys key key_len ref rows Extra 2501 PRIMARY CountryLanguage ref PRIMARY,Language Language 30 const 19 Using index condition 2511 PRIMARY Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where 2522 DEPENDENT SUBQUERY CountryLanguage unique_subquery PRIMARY,Language PRIMARY 33 func,const 1 Using index; Using where 253SELECT Country.Name 254FROM Country, CountryLanguage 255WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English') 256AND CountryLanguage.Language = 'French' 257 AND Code = Country; 258Name 259France 260Saint Pierre and Miquelon 261Belgium 262Burundi 263Guadeloupe 264Haiti 265Madagascar 266Martinique 267Mayotte 268French Polynesia 269Rwanda 270Sao Tome and Principe 271Switzerland 272New Caledonia 273Lebanon 274Mauritius 275Andorra 276Italy 277Luxembourg 278Q2.2m: 279Countries that speak French OR Spanish, but do not speak English 280MATERIALIZATION because the outer query filters less rows than Q5-a, 281so there are more lookups. 282set statement optimizer_switch='rowid_filter=off' for 283EXPLAIN 284SELECT Country.Name 285FROM Country, CountryLanguage 286WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English') 287AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish') 288AND Code = Country; 289id select_type table type possible_keys key key_len ref rows Extra 2901 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 44 Using index condition; Rowid-ordered scan 2911 PRIMARY Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where 2923 MATERIALIZED CountryLanguage ref PRIMARY,Language Language 30 const 48 Using index condition 293set statement optimizer_switch='rowid_filter=off' for 294SELECT Country.Name 295FROM Country, CountryLanguage 296WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English') 297AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish') 298AND Code = Country; 299Name 300Andorra 301Argentina 302Bolivia 303Chile 304Costa Rica 305Dominican Republic 306Ecuador 307El Salvador 308Spain 309Guatemala 310Honduras 311Colombia 312Cuba 313Mexico 314Nicaragua 315Panama 316Paraguay 317Peru 318France 319Saint Pierre and Miquelon 320Uruguay 321Venezuela 322Belgium 323Burundi 324Guadeloupe 325Haiti 326Madagascar 327Martinique 328Mayotte 329French Polynesia 330Rwanda 331Sao Tome and Principe 332Switzerland 333New Caledonia 334Lebanon 335Mauritius 336Andorra 337Italy 338Luxembourg 339France 340Sweden 341 342Q2.3e: 343Not a very meaningful query that tests NOT IN. 344IN-EXISTS because the outer query is cheap enough to reexecute many times. 345EXPLAIN 346select count(*) 347from CountryLanguage 348where (Language, Country) NOT IN 349(SELECT City.Name, Country.Code 350FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000)) 351AND Language IN ('English','Spanish'); 352id select_type table type possible_keys key key_len ref rows Extra 3531 PRIMARY CountryLanguage range Language Language 30 NULL 73 Using index condition; Using where; Rowid-ordered scan 3542 DEPENDENT SUBQUERY City ref CityName CityName 35 func 1 Using index condition 3552 DEPENDENT SUBQUERY Country eq_ref PRIMARY PRIMARY 3 world.City.Country 1 Using where; Using index 356select count(*) 357from CountryLanguage 358where (Language, Country) NOT IN 359(SELECT City.Name, Country.Code 360FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000)) 361AND Language IN ('English','Spanish'); 362count(*) 36388 364Q2.3m: 365MATERIALIZATION with the PARTIAL_MATCH_MERGE strategy, because the HAVING 366clause prevents the use of the index on City(Name), and in practice reduces 367radically the size of the temp table. 368EXPLAIN 369select count(*) 370from CountryLanguage 371where (Language, Country) NOT IN 372(SELECT City.Name, Country.Code 373FROM City LEFT JOIN Country ON (Country = Code) 374HAVING City.Name LIKE "Santa%"); 375id select_type table type possible_keys key key_len ref rows Extra 3761 PRIMARY CountryLanguage index NULL PRIMARY 33 NULL 984 Using where; Using index 3772 MATERIALIZED City ALL NULL NULL NULL NULL 4079 3782 MATERIALIZED Country eq_ref PRIMARY PRIMARY 3 world.City.Country 1 Using index 379select count(*) 380from CountryLanguage 381where (Language, Country) NOT IN 382(SELECT City.Name, Country.Code 383FROM City LEFT JOIN Country ON (Country = Code) 384HAVING City.Name LIKE "Santa%"); 385count(*) 386984 387 3883. Subqueries with GROUP BY, HAVING, and aggregate functions 389 390Q3.1: 391Languages that are spoken in countries with 10 or 11 languages 392MATERIALIZATION is about 100 times faster than IN-EXISTS. 393EXPLAIN 394select count(*) 395from CountryLanguage 396where 397(Country, 10) IN (SELECT Code, COUNT(*) FROM CountryLanguage, Country 398WHERE Code = Country GROUP BY Code) 399OR 400(Country, 11) IN (SELECT Code, COUNT(*) FROM CountryLanguage, Country 401WHERE Code = Country GROUP BY Code) 402order by Country; 403id select_type table type possible_keys key key_len ref rows Extra 4041 PRIMARY CountryLanguage index NULL PRIMARY 33 NULL 984 Using where; Using index 4053 MATERIALIZED Country index PRIMARY PRIMARY 3 NULL 239 Using index 4063 MATERIALIZED CountryLanguage ref PRIMARY PRIMARY 3 world.Country.Code 4 Using index 4072 MATERIALIZED Country index PRIMARY PRIMARY 3 NULL 239 Using index 4082 MATERIALIZED CountryLanguage ref PRIMARY PRIMARY 3 world.Country.Code 4 Using index 409select count(*) 410from CountryLanguage 411where 412(Country, 10) IN (SELECT Code, COUNT(*) FROM CountryLanguage, Country 413WHERE Code = Country GROUP BY Code) 414OR 415(Country, 11) IN (SELECT Code, COUNT(*) FROM CountryLanguage, Country 416WHERE Code = Country GROUP BY Code) 417order by Country; 418count(*) 419102 420 421Q3.2: 422Countries whose capital is a city name that names more than one 423cities. 424MATERIALIZATION because the cost of single subquery execution is 425close to that of materializing the subquery. 426EXPLAIN 427select * from Country, City 428where capital = id and 429(City.name in (SELECT name FROM City 430GROUP BY name HAVING Count(*) > 2) OR 431capital is null); 432id select_type table type possible_keys key key_len ref rows Extra 4331 PRIMARY Country ALL NULL NULL NULL NULL 239 Using where 4341 PRIMARY City eq_ref PRIMARY PRIMARY 4 world.Country.Capital 1 Using where 4352 MATERIALIZED City index NULL CityName 35 NULL 4079 Using index 436select * from Country, City 437where capital = id and 438(City.name in (SELECT name FROM City 439GROUP BY name HAVING Count(*) > 2) OR 440capital is null); 441Code Name SurfaceArea Population Capital ID Name Country population 442BMU Bermuda 53.00 65000 191 191 Hamilton BMU 1200 443BOL Bolivia 1098581.00 8329000 194 194 La Paz BOL 758141 444CRI Costa Rica 51100.00 4023000 584 584 San José CRI 339131 445HKG Hong Kong 1075.00 6782000 937 937 Victoria HKG 1312637 446SYC Seychelles 455.00 77000 3206 3206 Victoria SYC 41000 447 448Q3.3: MATERIALIZATION is 25 times faster than IN-EXISTS 449EXPLAIN 450SELECT Name 451FROM Country 452WHERE Country.Code NOT IN 453(SELECT Country FROM City GROUP BY Name HAVING COUNT(Name) = 1); 454id select_type table type possible_keys key key_len ref rows Extra 4551 PRIMARY Country ALL NULL NULL NULL NULL 239 Using where 4562 MATERIALIZED City ALL NULL NULL NULL NULL 4079 Using temporary 457SELECT Name 458FROM Country 459WHERE Country.Code NOT IN 460(SELECT Country FROM City GROUP BY Name HAVING COUNT(Name) = 1); 461Name 462Antigua and Barbuda 463Costa Rica 464Montserrat 465Norfolk Island 466Seychelles 467Antarctica 468Bouvet Island 469British Indian Ocean Territory 470South Georgia and the South Sandwich Islands 471Heard Island and McDonald Islands 472French Southern territories 473United States Minor Outlying Islands 474 4754. Subqueries in the SELECT and HAVING clauses 476 477Q4.1m: 478Capital information about very big cities 479MATERIALIZATION 480EXPLAIN 481select Name, City.id in (select capital from Country where capital is not null) as is_capital 482from City 483where City.population > 10000000; 484id select_type table type possible_keys key key_len ref rows Extra 4851 PRIMARY City range Population Population 5 NULL 3 Using index condition; Rowid-ordered scan 4862 MATERIALIZED Country ALL NULL NULL NULL NULL 239 Using where 487select Name, City.id in (select capital from Country where capital is not null) as is_capital 488from City 489where City.population > 10000000; 490Name is_capital 491Mumbai (Bombay) 0 492Q4.1e: 493IN-TO-EXISTS after adding an index to make the subquery re-execution 494efficient. 495create index CountryCapital on Country(capital); 496EXPLAIN 497select Name, City.id in (select capital from Country where capital is not null) as is_capital 498from City 499where City.population > 10000000; 500id select_type table type possible_keys key key_len ref rows Extra 5011 PRIMARY City range Population Population 5 NULL 3 Using index condition; Rowid-ordered scan 5022 SUBQUERY Country index_subquery CountryCapital CountryCapital 5 func 2 Using index; Using where 503select Name, City.id in (select capital from Country where capital is not null) as is_capital 504from City 505where City.population > 10000000; 506Name is_capital 507Mumbai (Bombay) 0 508drop index CountryCapital on Country; 509 510Q4.2: 511MATERIALIZATION 512EXPLAIN 513SELECT City.Name, City.Population 514FROM City JOIN Country ON City.Country = Country.Code 515GROUP BY City.Name 516HAVING City.Name IN (select Name from Country where population < 1000000); 517id select_type table type possible_keys key key_len ref rows Extra 5181 PRIMARY Country index PRIMARY PRIMARY 3 NULL 239 Using index; Using temporary; Using filesort 5191 PRIMARY City ref Country Country 3 world.Country.Code 17 5202 MATERIALIZED Country ALL Name NULL NULL NULL 239 Using where 521SELECT City.Name, City.Population 522FROM City JOIN Country ON City.Country = Country.Code 523GROUP BY City.Name 524HAVING City.Name IN (select Name from Country where population < 1000000); 525Name Population 526Djibouti 383000 527Gibraltar 27025 528Macao 437500 529San Marino 2294 530 5315. Subqueries with UNION 532 533Q5.1: 534EXPLAIN 535SELECT * from City where (Name, 91) in 536(SELECT Name, round(Population/1000) 537FROM City 538WHERE Country = "IND" AND Population > 2500000 539UNION 540SELECT Name, round(Population/1000) 541FROM City 542WHERE Country = "IND" AND Population < 100000); 543id select_type table type possible_keys key key_len ref rows Extra 5441 PRIMARY City ALL NULL NULL NULL NULL 4079 Using where 5452 DEPENDENT SUBQUERY City ref Population,Country,CityName CityName 35 func 1 Using where 5463 DEPENDENT UNION City ref Population,Country,CityName CityName 35 func 1 Using where 547NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 548SELECT * from City where (Name, 91) in 549(SELECT Name, round(Population/1000) 550FROM City 551WHERE Country = "IND" AND Population > 2500000 552UNION 553SELECT Name, round(Population/1000) 554FROM City 555WHERE Country = "IND" AND Population < 100000); 556ID Name Country population 5571359 Hassan IND 90803 5581360 Ambala Sadar IND 90712 5591361 Baidyabati IND 90601 560set @@optimizer_switch='default'; 561drop database world; 562 563 564TEST GROUP 2: 565Tests of various combinations of optimizer switches, types of queries, 566available indexes, column nullability, constness of tables/predicates. 567===================================================================== 568set optimizer_switch=default; 569