1SET SESSION DEFAULT_STORAGE_ENGINE='InnoDB'; 2set @innodb_stats_persistent_save= @@innodb_stats_persistent; 3set @innodb_stats_persistent_sample_pages_save= 4@@innodb_stats_persistent_sample_pages; 5set global innodb_stats_persistent= 1; 6set global innodb_stats_persistent_sample_pages=100; 7DROP TABLE IF EXISTS t1,t2,t3,t4; 8DROP DATABASE IF EXISTS world; 9set names utf8; 10CREATE DATABASE world; 11use world; 12CREATE TABLE Country ( 13Code char(3) NOT NULL default '', 14Name char(52) NOT NULL default '', 15SurfaceArea float(10,2) NOT NULL default '0.00', 16Population int(11) NOT NULL default '0', 17Capital int(11) default NULL, 18PRIMARY KEY (Code), 19UNIQUE INDEX (Name) 20); 21CREATE TABLE City ( 22ID int(11) NOT NULL auto_increment, 23Name char(35) NOT NULL default '', 24Country char(3) NOT NULL default '', 25Population int(11) NOT NULL default '0', 26PRIMARY KEY (ID), 27INDEX (Population), 28INDEX (Country) 29); 30CREATE TABLE CountryLanguage ( 31Country char(3) NOT NULL default '', 32Language char(30) NOT NULL default '', 33Percentage float(3,1) NOT NULL default '0.0', 34PRIMARY KEY (Country, Language), 35INDEX (Percentage) 36); 37SELECT COUNT(*) FROM Country; 38COUNT(*) 39239 40SELECT COUNT(*) FROM City; 41COUNT(*) 424079 43SELECT COUNT(*) FROM CountryLanguage; 44COUNT(*) 45984 46CREATE INDEX Name ON City(Name); 47SET SESSION optimizer_switch='rowid_filter=off'; 48SET SESSION optimizer_switch='index_merge_sort_intersection=on'; 49SELECT COUNT(*) FROM City; 50COUNT(*) 514079 52SELECT COUNT(*) FROM City WHERE Name LIKE 'C%'; 53COUNT(*) 54281 55SELECT COUNT(*) FROM City WHERE Name LIKE 'M%'; 56COUNT(*) 57301 58SELECT COUNT(*) FROM City WHERE Population > 1000000; 59COUNT(*) 60237 61SELECT COUNT(*) FROM City WHERE Population > 1500000; 62COUNT(*) 63129 64SELECT COUNT(*) FROM City WHERE Population > 300000; 65COUNT(*) 661062 67SELECT COUNT(*) FROM City WHERE Population > 7000000; 68COUNT(*) 6914 70EXPLAIN 71SELECT * FROM City WHERE 72Name LIKE 'C%' AND Population > 1000000; 73id select_type table type possible_keys key key_len ref rows Extra 741 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where 75EXPLAIN 76SELECT * FROM City WHERE 77Name LIKE 'M%' AND Population > 1500000; 78id select_type table type possible_keys key key_len ref rows Extra 791 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where 80EXPLAIN 81SELECT * FROM City 82WHERE Name LIKE 'M%' AND Population > 300000; 83id select_type table type possible_keys key key_len ref rows Extra 841 SIMPLE City range Population,Name Name 35 NULL # Using index condition; Using where 85EXPLAIN 86SELECT * FROM City 87WHERE Name LIKE 'M%' AND Population > 7000000; 88id select_type table type possible_keys key key_len ref rows Extra 891 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where 90SELECT * FROM City USE INDEX () 91WHERE Name LIKE 'C%' AND Population > 1000000; 92ID Name Country Population 931026 Calcutta [Kolkata] IND 4399819 941027 Chennai (Madras) IND 3841396 95151 Chittagong BGD 1392860 961892 Chongqing CHN 6351600 971898 Chengdu CHN 3361500 981900 Changchun CHN 2812000 991910 Changsha CHN 1809800 100212 Curitiba BRA 1584232 1012258 Cali COL 2077386 1022485 Casablanca MAR 2940623 1032515 Ciudad de México MEX 8591309 1043539 Caracas VEN 1975294 1053795 Chicago USA 2896016 106608 Cairo EGY 6789479 10771 Córdoba ARG 1157507 108712 Cape Town ZAF 2352121 109926 Conakry GIN 1090610 110SELECT * FROM City 111WHERE Name LIKE 'C%' AND Population > 1000000; 112ID Name Country Population 1131026 Calcutta [Kolkata] IND 4399819 1141027 Chennai (Madras) IND 3841396 115151 Chittagong BGD 1392860 1161892 Chongqing CHN 6351600 1171898 Chengdu CHN 3361500 1181900 Changchun CHN 2812000 1191910 Changsha CHN 1809800 120212 Curitiba BRA 1584232 1212258 Cali COL 2077386 1222485 Casablanca MAR 2940623 1232515 Ciudad de México MEX 8591309 1243539 Caracas VEN 1975294 1253795 Chicago USA 2896016 126608 Cairo EGY 6789479 12771 Córdoba ARG 1157507 128712 Cape Town ZAF 2352121 129926 Conakry GIN 1090610 130SELECT * FROM City USE INDEX () 131WHERE Name LIKE 'M%' AND Population > 1500000; 132ID Name Country Population 1331024 Mumbai (Bombay) IND 10500000 134131 Melbourne AUS 2865329 1351381 Mashhad IRN 1887405 1362259 Medellín COL 1861265 1373520 Minsk BLR 1674000 1383580 Moscow RUS 8389200 139653 Madrid ESP 2879052 140766 Manila PHL 1581082 141942 Medan IDN 1843919 142SELECT * FROM City 143WHERE Name LIKE 'M%' AND Population > 1500000; 144ID Name Country Population 1451024 Mumbai (Bombay) IND 10500000 146131 Melbourne AUS 2865329 1471381 Mashhad IRN 1887405 1482259 Medellín COL 1861265 1493520 Minsk BLR 1674000 1503580 Moscow RUS 8389200 151653 Madrid ESP 2879052 152766 Manila PHL 1581082 153942 Medan IDN 1843919 154SELECT * FROM City USE INDEX () 155WHERE Name LIKE 'M%' AND Population > 300000; 156ID Name Country Population 1571024 Mumbai (Bombay) IND 10500000 1581042 Madurai IND 977856 1591051 Meerut IND 753778 1601074 Mysore IND 480692 1611081 Moradabad IND 429214 1621098 Malegaon IND 342595 163131 Melbourne AUS 2865329 1641366 Mosul IRQ 879000 1651381 Mashhad IRN 1887405 1661465 Milano ITA 1300977 1671559 Matsuyama JPN 466133 1681560 Matsudo JPN 461126 1691578 Machida JPN 364197 1701595 Miyazaki JPN 303784 1711810 Montréal CAN 1016376 1721816 Mississauga CAN 608072 1731882 Mombasa KEN 461753 1741945 Mudanjiang CHN 570000 1752005 Ma´anshan CHN 305421 176215 Manaus BRA 1255049 177223 Maceió BRA 786288 1782259 Medellín COL 1861265 1792267 Manizales COL 337580 1802300 Mbuji-Mayi COD 806475 1812348 Masan KOR 441242 1822440 Monrovia LBR 850000 1832454 Macao MAC 437500 1842487 Marrakech MAR 621914 1852491 Meknès MAR 460000 186250 Mauá BRA 375055 1872523 Monterrey MEX 1108499 1882526 Mexicali MEX 764902 1892530 Mérida MEX 703324 1902537 Morelia MEX 619958 1912554 Matamoros MEX 416428 1922557 Mazatlán MEX 380265 193256 Moji das Cruzes BRA 339194 1942698 Maputo MOZ 1018938 1952699 Matola MOZ 424662 1962711 Mandalay MMR 885300 1972712 Moulmein (Mawlamyine) MMR 307900 1982734 Managua NIC 959000 1992756 Mushin NGA 333200 2002757 Maiduguri NGA 320000 2012826 Multan PAK 1182441 2022975 Marseille FRA 798430 2033070 Munich [München] DEU 1194560 2043086 Mannheim DEU 307730 2053175 Mekka SAU 965700 2063176 Medina SAU 608300 2073214 Mogadishu SOM 997000 2083364 Mersin (Içel) TUR 587212 2093371 Malatya TUR 330312 2103434 Mykolajiv UKR 508000 2113435 Mariupol UKR 490000 2123438 Makijivka UKR 384000 2133492 Montevideo URY 1236000 2143520 Minsk BLR 1674000 2153522 Mogiljov BLR 356000 2163540 Maracaíbo VEN 1304776 2173545 Maracay VEN 444443 2183547 Maturín VEN 319726 2193580 Moscow RUS 8389200 2203622 Magnitogorsk RUS 427900 2213625 Murmansk RUS 376300 2223636 Mahat?kala RUS 332800 2233810 Memphis USA 650100 2243811 Milwaukee USA 596974 2253834 Mesa USA 396375 2263837 Minneapolis USA 382618 2273839 Miami USA 362470 228462 Manchester GBR 430000 229653 Madrid ESP 2879052 230658 Málaga ESP 530553 231661 Murcia ESP 353504 232766 Manila PHL 1581082 23377 Mar del Plata ARG 512880 234778 Makati PHL 444867 235781 Marikina PHL 391170 236783 Muntinlupa PHL 379310 237786 Malabon PHL 338855 23880 Merlo ARG 463846 23983 Moreno ARG 356993 24087 Morón ARG 349246 241942 Medan IDN 1843919 242947 Malang IDN 716862 243962 Manado IDN 332288 244963 Mataram IDN 306600 245SELECT * FROM City 246WHERE Name LIKE 'M%' AND Population > 300000; 247ID Name Country Population 2481024 Mumbai (Bombay) IND 10500000 2491042 Madurai IND 977856 2501051 Meerut IND 753778 2511074 Mysore IND 480692 2521081 Moradabad IND 429214 2531098 Malegaon IND 342595 254131 Melbourne AUS 2865329 2551366 Mosul IRQ 879000 2561381 Mashhad IRN 1887405 2571465 Milano ITA 1300977 2581559 Matsuyama JPN 466133 2591560 Matsudo JPN 461126 2601578 Machida JPN 364197 2611595 Miyazaki JPN 303784 2621810 Montréal CAN 1016376 2631816 Mississauga CAN 608072 2641882 Mombasa KEN 461753 2651945 Mudanjiang CHN 570000 2662005 Ma´anshan CHN 305421 267215 Manaus BRA 1255049 268223 Maceió BRA 786288 2692259 Medellín COL 1861265 2702267 Manizales COL 337580 2712300 Mbuji-Mayi COD 806475 2722348 Masan KOR 441242 2732440 Monrovia LBR 850000 2742454 Macao MAC 437500 2752487 Marrakech MAR 621914 2762491 Meknès MAR 460000 277250 Mauá BRA 375055 2782523 Monterrey MEX 1108499 2792526 Mexicali MEX 764902 2802530 Mérida MEX 703324 2812537 Morelia MEX 619958 2822554 Matamoros MEX 416428 2832557 Mazatlán MEX 380265 284256 Moji das Cruzes BRA 339194 2852698 Maputo MOZ 1018938 2862699 Matola MOZ 424662 2872711 Mandalay MMR 885300 2882712 Moulmein (Mawlamyine) MMR 307900 2892734 Managua NIC 959000 2902756 Mushin NGA 333200 2912757 Maiduguri NGA 320000 2922826 Multan PAK 1182441 2932975 Marseille FRA 798430 2943070 Munich [München] DEU 1194560 2953086 Mannheim DEU 307730 2963175 Mekka SAU 965700 2973176 Medina SAU 608300 2983214 Mogadishu SOM 997000 2993364 Mersin (Içel) TUR 587212 3003371 Malatya TUR 330312 3013434 Mykolajiv UKR 508000 3023435 Mariupol UKR 490000 3033438 Makijivka UKR 384000 3043492 Montevideo URY 1236000 3053520 Minsk BLR 1674000 3063522 Mogiljov BLR 356000 3073540 Maracaíbo VEN 1304776 3083545 Maracay VEN 444443 3093547 Maturín VEN 319726 3103580 Moscow RUS 8389200 3113622 Magnitogorsk RUS 427900 3123625 Murmansk RUS 376300 3133636 Mahat?kala RUS 332800 3143810 Memphis USA 650100 3153811 Milwaukee USA 596974 3163834 Mesa USA 396375 3173837 Minneapolis USA 382618 3183839 Miami USA 362470 319462 Manchester GBR 430000 320653 Madrid ESP 2879052 321658 Málaga ESP 530553 322661 Murcia ESP 353504 323766 Manila PHL 1581082 32477 Mar del Plata ARG 512880 325778 Makati PHL 444867 326781 Marikina PHL 391170 327783 Muntinlupa PHL 379310 328786 Malabon PHL 338855 32980 Merlo ARG 463846 33083 Moreno ARG 356993 33187 Morón ARG 349246 332942 Medan IDN 1843919 333947 Malang IDN 716862 334962 Manado IDN 332288 335963 Mataram IDN 306600 336SELECT * FROM City USE INDEX () 337WHERE Name LIKE 'M%' AND Population > 7000000; 338ID Name Country Population 3391024 Mumbai (Bombay) IND 10500000 3403580 Moscow RUS 8389200 341SELECT * FROM City 342WHERE Name LIKE 'M%' AND Population > 7000000; 343ID Name Country Population 3441024 Mumbai (Bombay) IND 10500000 3453580 Moscow RUS 8389200 346SELECT COUNT(*) FROM City WHERE Name BETWEEN 'M' AND 'N'; 347COUNT(*) 348301 349SELECT COUNT(*) FROM City WHERE Name BETWEEN 'G' AND 'J'; 350COUNT(*) 351408 352SELECT COUNT(*) FROM City WHERE Name BETWEEN 'G' AND 'K'; 353COUNT(*) 354512 355SELECT COUNT(*) FROM City WHERE Population > 1000000; 356COUNT(*) 357237 358SELECT COUNT(*) FROM City WHERE Population > 500000; 359COUNT(*) 360539 361SELECT COUNT(*) FROM City WHERE Country LIKE 'C%'; 362COUNT(*) 363551 364SELECT COUNT(*) FROM City WHERE Country LIKE 'B%'; 365COUNT(*) 366339 367SELECT COUNT(*) FROM City WHERE Country LIKE 'J%'; 368COUNT(*) 369256 370EXPLAIN 371SELECT * FROM City 372WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%'; 373id select_type table type possible_keys key key_len ref rows Extra 3741 SIMPLE City index_merge Population,Country,Name Population,Name,Country 4,35,3 NULL # Using sort_intersect(Population,Name,Country); Using where 375EXPLAIN 376SELECT * FROM City 377WHERE Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%'; 378id select_type table type possible_keys key key_len ref rows Extra 3791 SIMPLE City index_merge Population,Country,Name Population,Country,Name 4,3,35 NULL # Using sort_intersect(Population,Country,Name); Using where 380EXPLAIN 381SELECT * FROM City 382WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; 383id select_type table type possible_keys key key_len ref rows Extra 3841 SIMPLE City range Population,Name,Country Name # NULL # Using index condition; Using where 385SELECT * FROM City USE INDEX () 386WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%'; 387ID Name Country Population 3881810 Montréal CAN 1016376 3892259 Medellín COL 1861265 390SELECT * FROM City 391WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%'; 392ID Name Country Population 3931810 Montréal CAN 1016376 3942259 Medellín COL 1861265 395SELECT * FROM City USE INDEX () 396WHERE Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%'; 397ID Name Country Population 3981533 Jokohama [Yokohama] JPN 3339594 3991541 Hiroshima JPN 1119117 400SELECT * FROM City 401WHERE Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%'; 402ID Name Country Population 4031533 Jokohama [Yokohama] JPN 3339594 4041541 Hiroshima JPN 1119117 405SELECT * FROM City USE INDEX () 406WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; 407ID Name Country Population 4081895 Harbin CHN 4289800 4091904 Jinan CHN 2278100 4101905 Hangzhou CHN 2190500 4111914 Guiyang CHN 1465200 4121916 Hefei CHN 1369100 4131923 Jilin CHN 1040000 4141927 Hohhot CHN 916700 4151928 Handan CHN 840000 4161937 Huainan CHN 700000 4171938 Jixi CHN 683885 4181944 Jinzhou CHN 570000 4191950 Hegang CHN 520000 420SELECT * FROM City 421WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; 422ID Name Country Population 4231895 Harbin CHN 4289800 4241904 Jinan CHN 2278100 4251905 Hangzhou CHN 2190500 4261914 Guiyang CHN 1465200 4271916 Hefei CHN 1369100 4281923 Jilin CHN 1040000 4291927 Hohhot CHN 916700 4301928 Handan CHN 840000 4311937 Huainan CHN 700000 4321938 Jixi CHN 683885 4331944 Jinzhou CHN 570000 4341950 Hegang CHN 520000 435SELECT COUNT(*) FROM City WHERE ID BETWEEN 501 AND 1000; 436COUNT(*) 437500 438SELECT COUNT(*) FROM City WHERE ID BETWEEN 1 AND 500; 439COUNT(*) 440500 441SELECT COUNT(*) FROM City WHERE ID BETWEEN 2001 AND 2500; 442COUNT(*) 443500 444SELECT COUNT(*) FROM City WHERE ID BETWEEN 3701 AND 4000; 445COUNT(*) 446300 447SELECT COUNT(*) FROM City WHERE Population > 700000; 448COUNT(*) 449358 450SELECT COUNT(*) FROM City WHERE Population > 1000000; 451COUNT(*) 452237 453SELECT COUNT(*) FROM City WHERE Population > 300000; 454COUNT(*) 4551062 456SELECT COUNT(*) FROM City WHERE Population > 600000; 457COUNT(*) 458428 459SELECT COUNT(*) FROM City WHERE Country LIKE 'C%'; 460COUNT(*) 461551 462SELECT COUNT(*) FROM City WHERE Country LIKE 'A%'; 463COUNT(*) 464107 465SELECT COUNT(*) FROM City WHERE Country LIKE 'H%'; 466COUNT(*) 46722 468SELECT COUNT(*) FROM City WHERE Country BETWEEN 'S' AND 'Z'; 469COUNT(*) 470682 471EXPLAIN 472SELECT * FROM City 473WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; 474id select_type table type possible_keys key key_len ref rows Extra 4751 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,7 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where 476EXPLAIN 477SELECT * FROM City 478WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%'; 479id select_type table type possible_keys key key_len ref rows Extra 4801 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,7 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where 481EXPLAIN 482SELECT * FROM City 483WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; 484id select_type table type possible_keys key key_len ref rows Extra 4851 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,7 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where 486EXPLAIN 487SELECT * FROM City 488WHERE ID BETWEEN 3701 AND 4000 AND Population > 1000000 489AND Country BETWEEN 'S' AND 'Z'; 490id select_type table type possible_keys key key_len ref rows Extra 4911 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,7 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where 492EXPLAIN 493SELECT * FROM City 494WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 495AND Country BETWEEN 'S' AND 'Z' ; 496id select_type table type possible_keys key key_len ref rows Extra 4971 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,7 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where 498SELECT * FROM City USE INDEX () 499WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; 500ID Name Country Population 501554 Santiago de Chile CHL 4703954 502SELECT * FROM City 503WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; 504ID Name Country Population 505554 Santiago de Chile CHL 4703954 506SELECT * FROM City USE INDEX () 507WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%'; 508ID Name Country Population 509SELECT * FROM City 510WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%'; 511ID Name Country Population 512SELECT * FROM City USE INDEX () 513WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; 514ID Name Country Population 5152409 Zagreb HRV 706770 516SELECT * FROM City 517WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; 518ID Name Country Population 5192409 Zagreb HRV 706770 520SELECT * FROM City USE INDEX () 521WHERE ID BETWEEN 3701 AND 4000 AND Population > 700000 522AND Country BETWEEN 'S' AND 'Z'; 523ID Name Country Population 5243769 Ho Chi Minh City VNM 3980000 5253770 Hanoi VNM 1410000 5263771 Haiphong VNM 783133 5273793 New York USA 8008278 5283794 Los Angeles USA 3694820 5293795 Chicago USA 2896016 5303796 Houston USA 1953631 5313797 Philadelphia USA 1517550 5323798 Phoenix USA 1321045 5333799 San Diego USA 1223400 5343800 Dallas USA 1188580 5353801 San Antonio USA 1144646 5363802 Detroit USA 951270 5373803 San Jose USA 894943 5383804 Indianapolis USA 791926 5393805 San Francisco USA 776733 5403806 Jacksonville USA 735167 5413807 Columbus USA 711470 542SELECT * FROM City 543WHERE ID BETWEEN 3701 AND 4000 AND Population > 700000 544AND Country BETWEEN 'S' AND 'Z'; 545ID Name Country Population 5463769 Ho Chi Minh City VNM 3980000 5473770 Hanoi VNM 1410000 5483771 Haiphong VNM 783133 5493793 New York USA 8008278 5503794 Los Angeles USA 3694820 5513795 Chicago USA 2896016 5523796 Houston USA 1953631 5533797 Philadelphia USA 1517550 5543798 Phoenix USA 1321045 5553799 San Diego USA 1223400 5563800 Dallas USA 1188580 5573801 San Antonio USA 1144646 5583802 Detroit USA 951270 5593803 San Jose USA 894943 5603804 Indianapolis USA 791926 5613805 San Francisco USA 776733 5623806 Jacksonville USA 735167 5633807 Columbus USA 711470 564SELECT * FROM City USE INDEX () 565WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 566AND Country BETWEEN 'S' AND 'Z' ; 567ID Name Country Population 5683048 Stockholm SWE 750348 5693173 Riyadh SAU 3324000 5703174 Jedda SAU 2046300 5713175 Mekka SAU 965700 5723176 Medina SAU 608300 5733197 Pikine SEN 855287 5743198 Dakar SEN 785071 5753207 Freetown SLE 850000 5763208 Singapore SGP 4017733 5773214 Mogadishu SOM 997000 5783224 Omdurman SDN 1271403 5793225 Khartum SDN 947483 5803226 Sharq al-Nil SDN 700887 5813250 Damascus SYR 1347000 5823251 Aleppo SYR 1261983 5833263 Taipei TWN 2641312 5843264 Kaohsiung TWN 1475505 5853265 Taichung TWN 940589 5863266 Tainan TWN 728060 5873305 Dar es Salaam TZA 1747000 5883320 Bangkok THA 6320174 5893349 Tunis TUN 690600 5903357 Istanbul TUR 8787958 5913358 Ankara TUR 3038159 5923359 Izmir TUR 2130359 5933360 Adana TUR 1131198 5943361 Bursa TUR 1095842 5953362 Gaziantep TUR 789056 5963363 Konya TUR 628364 5973425 Kampala UGA 890800 5983426 Kyiv UKR 2624000 5993427 Harkova [Harkiv] UKR 1500000 6003428 Dnipropetrovsk UKR 1103000 6013429 Donetsk UKR 1050000 6023430 Odesa UKR 1011000 6033431 Zaporizzja UKR 848000 6043432 Lviv UKR 788000 6053433 Kryvyi Rig UKR 703000 6063492 Montevideo URY 1236000 6073503 Toskent UZB 2117500 6083539 Caracas VEN 1975294 6093540 Maracaíbo VEN 1304776 6103541 Barquisimeto VEN 877239 6113542 Valencia VEN 794246 6123543 Ciudad Guayana VEN 663713 6133769 Ho Chi Minh City VNM 3980000 6143770 Hanoi VNM 1410000 6153771 Haiphong VNM 783133 6163793 New York USA 8008278 6173794 Los Angeles USA 3694820 6183795 Chicago USA 2896016 6193796 Houston USA 1953631 6203797 Philadelphia USA 1517550 6213798 Phoenix USA 1321045 6223799 San Diego USA 1223400 6233800 Dallas USA 1188580 6243801 San Antonio USA 1144646 6253802 Detroit USA 951270 6263803 San Jose USA 894943 6273804 Indianapolis USA 791926 6283805 San Francisco USA 776733 6293806 Jacksonville USA 735167 6303807 Columbus USA 711470 6313808 Austin USA 656562 6323809 Baltimore USA 651154 6333810 Memphis USA 650100 634SELECT * FROM City 635WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 636AND Country BETWEEN 'S' AND 'Z' ; 637ID Name Country Population 6383048 Stockholm SWE 750348 6393173 Riyadh SAU 3324000 6403174 Jedda SAU 2046300 6413175 Mekka SAU 965700 6423176 Medina SAU 608300 6433197 Pikine SEN 855287 6443198 Dakar SEN 785071 6453207 Freetown SLE 850000 6463208 Singapore SGP 4017733 6473214 Mogadishu SOM 997000 6483224 Omdurman SDN 1271403 6493225 Khartum SDN 947483 6503226 Sharq al-Nil SDN 700887 6513250 Damascus SYR 1347000 6523251 Aleppo SYR 1261983 6533263 Taipei TWN 2641312 6543264 Kaohsiung TWN 1475505 6553265 Taichung TWN 940589 6563266 Tainan TWN 728060 6573305 Dar es Salaam TZA 1747000 6583320 Bangkok THA 6320174 6593349 Tunis TUN 690600 6603357 Istanbul TUR 8787958 6613358 Ankara TUR 3038159 6623359 Izmir TUR 2130359 6633360 Adana TUR 1131198 6643361 Bursa TUR 1095842 6653362 Gaziantep TUR 789056 6663363 Konya TUR 628364 6673425 Kampala UGA 890800 6683426 Kyiv UKR 2624000 6693427 Harkova [Harkiv] UKR 1500000 6703428 Dnipropetrovsk UKR 1103000 6713429 Donetsk UKR 1050000 6723430 Odesa UKR 1011000 6733431 Zaporizzja UKR 848000 6743432 Lviv UKR 788000 6753433 Kryvyi Rig UKR 703000 6763492 Montevideo URY 1236000 6773503 Toskent UZB 2117500 6783539 Caracas VEN 1975294 6793540 Maracaíbo VEN 1304776 6803541 Barquisimeto VEN 877239 6813542 Valencia VEN 794246 6823543 Ciudad Guayana VEN 663713 6833769 Ho Chi Minh City VNM 3980000 6843770 Hanoi VNM 1410000 6853771 Haiphong VNM 783133 6863793 New York USA 8008278 6873794 Los Angeles USA 3694820 6883795 Chicago USA 2896016 6893796 Houston USA 1953631 6903797 Philadelphia USA 1517550 6913798 Phoenix USA 1321045 6923799 San Diego USA 1223400 6933800 Dallas USA 1188580 6943801 San Antonio USA 1144646 6953802 Detroit USA 951270 6963803 San Jose USA 894943 6973804 Indianapolis USA 791926 6983805 San Francisco USA 776733 6993806 Jacksonville USA 735167 7003807 Columbus USA 711470 7013808 Austin USA 656562 7023809 Baltimore USA 651154 7033810 Memphis USA 650100 704SET SESSION sort_buffer_size = IF(@@version_compile_machine like '%64%', 2048, 1536); 705EXPLAIN 706SELECT * FROM City WHERE 707Name LIKE 'C%' AND Population > 1000000; 708id select_type table type possible_keys key key_len ref rows Extra 7091 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where 710EXPLAIN 711SELECT * FROM City WHERE 712Name LIKE 'M%' AND Population > 1500000; 713id select_type table type possible_keys key key_len ref rows Extra 7141 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where 715EXPLAIN 716SELECT * FROM City 717WHERE Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%'; 718id select_type table type possible_keys key key_len ref rows Extra 7191 SIMPLE City index_merge Population,Country,Name Population,Country 4,3 NULL # Using sort_intersect(Population,Country); Using where 720EXPLAIN 721SELECT * FROM City 722WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%'; 723id select_type table type possible_keys key key_len ref rows Extra 7241 SIMPLE City range Population,Country,Name Name 35 NULL # Using index condition; Using where 725EXPLAIN 726SELECT * FROM City 727WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%'; 728id select_type table type possible_keys key key_len ref rows Extra 7291 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,7 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where 730EXPLAIN 731SELECT * FROM City 732WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 733AND Country BETWEEN 'S' AND 'Z'; 734id select_type table type possible_keys key key_len ref rows Extra 7351 SIMPLE City range PRIMARY,Population,Country PRIMARY 4 NULL # Using where 736SELECT * FROM City WHERE 737Name LIKE 'C%' AND Population > 1000000; 738ID Name Country Population 7391026 Calcutta [Kolkata] IND 4399819 7401027 Chennai (Madras) IND 3841396 741151 Chittagong BGD 1392860 7421892 Chongqing CHN 6351600 7431898 Chengdu CHN 3361500 7441900 Changchun CHN 2812000 7451910 Changsha CHN 1809800 746212 Curitiba BRA 1584232 7472258 Cali COL 2077386 7482485 Casablanca MAR 2940623 7492515 Ciudad de México MEX 8591309 7503539 Caracas VEN 1975294 7513795 Chicago USA 2896016 752608 Cairo EGY 6789479 75371 Córdoba ARG 1157507 754712 Cape Town ZAF 2352121 755926 Conakry GIN 1090610 756SELECT * FROM City WHERE 757Name LIKE 'M%' AND Population > 1500000; 758ID Name Country Population 7591024 Mumbai (Bombay) IND 10500000 760131 Melbourne AUS 2865329 7611381 Mashhad IRN 1887405 7622259 Medellín COL 1861265 7633520 Minsk BLR 1674000 7643580 Moscow RUS 8389200 765653 Madrid ESP 2879052 766766 Manila PHL 1581082 767942 Medan IDN 1843919 768SELECT * FROM City 769WHERE Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'J%'; 770ID Name Country Population 7711541 Hiroshima JPN 1119117 772SELECT * FROM City 773WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%'; 774ID Name Country Population 7751895 Harbin CHN 4289800 7761905 Hangzhou CHN 2190500 7771914 Guiyang CHN 1465200 7781916 Hefei CHN 1369100 7791927 Hohhot CHN 916700 7801928 Handan CHN 840000 7811937 Huainan CHN 700000 7821950 Hegang CHN 520000 783SELECT * FROM City 784WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%'; 785ID Name Country Population 786SELECT * FROM City 787WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 788AND Country BETWEEN 'S' AND 'Z'; 789ID Name Country Population 7903048 Stockholm SWE 750348 7913173 Riyadh SAU 3324000 7923174 Jedda SAU 2046300 7933175 Mekka SAU 965700 7943176 Medina SAU 608300 7953197 Pikine SEN 855287 7963198 Dakar SEN 785071 7973207 Freetown SLE 850000 7983208 Singapore SGP 4017733 7993214 Mogadishu SOM 997000 8003224 Omdurman SDN 1271403 8013225 Khartum SDN 947483 8023226 Sharq al-Nil SDN 700887 8033250 Damascus SYR 1347000 8043251 Aleppo SYR 1261983 8053263 Taipei TWN 2641312 8063264 Kaohsiung TWN 1475505 8073265 Taichung TWN 940589 8083266 Tainan TWN 728060 8093305 Dar es Salaam TZA 1747000 8103320 Bangkok THA 6320174 8113349 Tunis TUN 690600 8123357 Istanbul TUR 8787958 8133358 Ankara TUR 3038159 8143359 Izmir TUR 2130359 8153360 Adana TUR 1131198 8163361 Bursa TUR 1095842 8173362 Gaziantep TUR 789056 8183363 Konya TUR 628364 8193425 Kampala UGA 890800 8203426 Kyiv UKR 2624000 8213427 Harkova [Harkiv] UKR 1500000 8223428 Dnipropetrovsk UKR 1103000 8233429 Donetsk UKR 1050000 8243430 Odesa UKR 1011000 8253431 Zaporizzja UKR 848000 8263432 Lviv UKR 788000 8273433 Kryvyi Rig UKR 703000 8283492 Montevideo URY 1236000 8293503 Toskent UZB 2117500 8303539 Caracas VEN 1975294 8313540 Maracaíbo VEN 1304776 8323541 Barquisimeto VEN 877239 8333542 Valencia VEN 794246 8343543 Ciudad Guayana VEN 663713 8353769 Ho Chi Minh City VNM 3980000 8363770 Hanoi VNM 1410000 8373771 Haiphong VNM 783133 8383793 New York USA 8008278 8393794 Los Angeles USA 3694820 8403795 Chicago USA 2896016 8413796 Houston USA 1953631 8423797 Philadelphia USA 1517550 8433798 Phoenix USA 1321045 8443799 San Diego USA 1223400 8453800 Dallas USA 1188580 8463801 San Antonio USA 1144646 8473802 Detroit USA 951270 8483803 San Jose USA 894943 8493804 Indianapolis USA 791926 8503805 San Francisco USA 776733 8513806 Jacksonville USA 735167 8523807 Columbus USA 711470 8533808 Austin USA 656562 8543809 Baltimore USA 651154 8553810 Memphis USA 650100 856SET SESSION sort_buffer_size = default; 857DROP INDEX Country ON City; 858CREATE INDEX CountryID ON City(Country,ID); 859CREATE INDEX CountryName ON City(Country,Name); 860EXPLAIN 861SELECT * FROM City 862WHERE Country LIKE 'M%' AND Population > 1000000; 863id select_type table type possible_keys key key_len ref rows Extra 8641 SIMPLE City index_merge Population,CountryID,CountryName Population,CountryID 4,3 NULL # Using sort_intersect(Population,CountryID); Using where 865EXPLAIN 866SELECT * FROM City 867WHERE Country='USA' AND Population > 1000000; 868id select_type table type possible_keys key key_len ref rows Extra 8691 SIMPLE City index_merge Population,CountryID,CountryName Population,CountryID 4,3 NULL # Using sort_intersect(Population,CountryID); Using where 870EXPLAIN 871SELECT * FROM City 872WHERE Country='USA' AND Population > 1500000 AND Name LIKE 'C%'; 873id select_type table type possible_keys key key_len ref rows Extra 8741 SIMPLE City index_merge Population,Name,CountryID,CountryName CountryName,Population 38,4 NULL # Using sort_intersect(CountryName,Population); Using where 875SELECT * FROM City USE INDEX () 876WHERE Country LIKE 'M%' AND Population > 1000000; 877ID Name Country Population 8782464 Kuala Lumpur MYS 1297526 8792485 Casablanca MAR 2940623 8802515 Ciudad de México MEX 8591309 8812516 Guadalajara MEX 1647720 8822517 Ecatepec de Morelos MEX 1620303 8832518 Puebla MEX 1346176 8842519 Nezahualcóyotl MEX 1224924 8852520 Juárez MEX 1217818 8862521 Tijuana MEX 1212232 8872522 León MEX 1133576 8882523 Monterrey MEX 1108499 8892524 Zapopan MEX 1002239 8902698 Maputo MOZ 1018938 8912710 Rangoon (Yangon) MMR 3361700 892SELECT * FROM City 893WHERE Country LIKE 'M%' AND Population > 1000000; 894ID Name Country Population 8952464 Kuala Lumpur MYS 1297526 8962485 Casablanca MAR 2940623 8972515 Ciudad de México MEX 8591309 8982516 Guadalajara MEX 1647720 8992517 Ecatepec de Morelos MEX 1620303 9002518 Puebla MEX 1346176 9012519 Nezahualcóyotl MEX 1224924 9022520 Juárez MEX 1217818 9032521 Tijuana MEX 1212232 9042522 León MEX 1133576 9052523 Monterrey MEX 1108499 9062524 Zapopan MEX 1002239 9072698 Maputo MOZ 1018938 9082710 Rangoon (Yangon) MMR 3361700 909SELECT * FROM City USE INDEX () 910WHERE Country='USA' AND Population > 1000000; 911ID Name Country Population 9123793 New York USA 8008278 9133794 Los Angeles USA 3694820 9143795 Chicago USA 2896016 9153796 Houston USA 1953631 9163797 Philadelphia USA 1517550 9173798 Phoenix USA 1321045 9183799 San Diego USA 1223400 9193800 Dallas USA 1188580 9203801 San Antonio USA 1144646 921SELECT * FROM City 922WHERE Country='USA' AND Population > 1000000; 923ID Name Country Population 9243793 New York USA 8008278 9253794 Los Angeles USA 3694820 9263795 Chicago USA 2896016 9273796 Houston USA 1953631 9283797 Philadelphia USA 1517550 9293798 Phoenix USA 1321045 9303799 San Diego USA 1223400 9313800 Dallas USA 1188580 9323801 San Antonio USA 1144646 933SELECT * FROM City USE INDEX () 934WHERE Country='USA' AND Population > 1500000 AND Name LIKE 'C%'; 935ID Name Country Population 9363795 Chicago USA 2896016 937SELECT * FROM City 938WHERE Country='USA' AND Population > 1500000 AND Name LIKE 'C%'; 939ID Name Country Population 9403795 Chicago USA 2896016 941EXPLAIN 942SELECT * FROM City, Country 943WHERE City.Name LIKE 'C%' AND City.Population > 1000000 AND 944Country.Code=City.Country; 945id select_type table type possible_keys key key_len ref rows Extra 9461 SIMPLE City index_merge Population,Name,CountryID,CountryName Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where 9471 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.City.Country # 948DROP DATABASE world; 949use test; 950CREATE TABLE t1 ( 951f1 int, 952f4 varchar(32), 953f5 int, 954PRIMARY KEY (f1), 955KEY (f4) 956); 957INSERT INTO t1 VALUES 958(5,'H',1), (9,'g',0), (527,'i',0), (528,'y',1), (529,'S',6), 959(530,'m',7), (531,'b',2), (532,'N',1), (533,'V',NULL), (534,'l',1), 960(535,'M',0), (536,'w',1), (537,'j',5), (538,'l',0), (539,'n',2), 961(540,'m',2), (541,'r',2), (542,'l',2), (543,'h',3),(544,'o',0), 962(956,'h',0), (957,'g',0), (958,'W',5), (959,'s',3), (960,'w',0), 963(961,'q',0), (962,'e',NULL), (963,'u',7), (964,'q',1), (965,'N',NULL), 964(966,'e',0), (967,'t',3), (968,'e',6), (969,'f',NULL), (970,'j',0), 965(971,'s',3), (972,'I',0), (973,'h',4), (974,'g',1), (975,'s',0), 966(976,'r',3), (977,'x',1), (978,'v',8), (979,'j',NULL), (980,'z',7), 967(981,'t',9), (982,'j',5), (983,'u',NULL), (984,'g',6), (985,'w',1), 968(986,'h',1), (987,'v',0), (988,'v',0), (989,'c',2), (990,'b',7), 969(991,'z',0), (992,'M',1), (993,'u',2), (994,'r',2), (995,'b',4), 970(996,'A',2), (997,'u',0), (998,'a',0), (999,'j',2), (1,'I',2); 971EXPLAIN 972SELECT * FROM t1 973WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; 974id select_type table type possible_keys key key_len ref rows Extra 9751 SIMPLE t1 index_merge PRIMARY,f4 PRIMARY,f4 4,39 NULL # Using sort_intersect(PRIMARY,f4); Using where 976SELECT * FROM t1 977WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; 978f1 f4 f5 979994 r 2 980996 A 2 981998 a 0 982DROP TABLE t1; 983SET SESSION optimizer_switch='index_merge_sort_intersection=on'; 984SET SESSION optimizer_switch='rowid_filter=default'; 985set global innodb_stats_persistent= @innodb_stats_persistent_save; 986set global innodb_stats_persistent_sample_pages= 987@innodb_stats_persistent_sample_pages_save; 988SET SESSION DEFAULT_STORAGE_ENGINE=DEFAULT; 989