SET SESSION STORAGE_ENGINE='InnoDB'; DROP TABLE IF EXISTS t1,t2,t3,t4; DROP DATABASE IF EXISTS world; set names utf8; CREATE DATABASE world; use world; CREATE TABLE Country ( Code char(3) NOT NULL default '', Name char(52) NOT NULL default '', SurfaceArea float(10,2) NOT NULL default '0.00', Population int(11) NOT NULL default '0', Capital int(11) default NULL, PRIMARY KEY (Code), UNIQUE INDEX (Name) ); CREATE TABLE City ( ID int(11) NOT NULL auto_increment, Name char(35) NOT NULL default '', Country char(3) NOT NULL default '', Population int(11) NOT NULL default '0', PRIMARY KEY (ID), INDEX (Population), INDEX (Country) ); CREATE TABLE CountryLanguage ( Country char(3) NOT NULL default '', Language char(30) NOT NULL default '', Percentage float(3,1) NOT NULL default '0.0', PRIMARY KEY (Country, Language), INDEX (Percentage) ); SELECT COUNT(*) FROM Country; COUNT(*) 239 SELECT COUNT(*) FROM City; COUNT(*) 4079 SELECT COUNT(*) FROM CountryLanguage; COUNT(*) 984 CREATE INDEX Name ON City(Name); SET SESSION optimizer_switch='index_merge_sort_intersection=on'; SELECT COUNT(*) FROM City; COUNT(*) 4079 SELECT COUNT(*) FROM City WHERE Name LIKE 'C%'; COUNT(*) 281 SELECT COUNT(*) FROM City WHERE Name LIKE 'M%'; COUNT(*) 301 SELECT COUNT(*) FROM City WHERE Population > 1000000; COUNT(*) 237 SELECT COUNT(*) FROM City WHERE Population > 1500000; COUNT(*) 129 SELECT COUNT(*) FROM City WHERE Population > 300000; COUNT(*) 1062 SELECT COUNT(*) FROM City WHERE Population > 7000000; COUNT(*) 14 EXPLAIN SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where EXPLAIN SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 1500000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where EXPLAIN SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 300000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Name Name,Population 35,4 NULL # Using sort_intersect(Name,Population); Using where EXPLAIN SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 7000000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where SELECT * FROM City USE INDEX () WHERE Name LIKE 'C%' AND Population > 1000000; ID Name Country Population 1026 Calcutta [Kolkata] IND 4399819 1027 Chennai (Madras) IND 3841396 151 Chittagong BGD 1392860 1892 Chongqing CHN 6351600 1898 Chengdu CHN 3361500 1900 Changchun CHN 2812000 1910 Changsha CHN 1809800 212 Curitiba BRA 1584232 2258 Cali COL 2077386 2485 Casablanca MAR 2940623 2515 Ciudad de México MEX 8591309 3539 Caracas VEN 1975294 3795 Chicago USA 2896016 608 Cairo EGY 6789479 71 Córdoba ARG 1157507 712 Cape Town ZAF 2352121 926 Conakry GIN 1090610 SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; ID Name Country Population 1026 Calcutta [Kolkata] IND 4399819 1027 Chennai (Madras) IND 3841396 151 Chittagong BGD 1392860 1892 Chongqing CHN 6351600 1898 Chengdu CHN 3361500 1900 Changchun CHN 2812000 1910 Changsha CHN 1809800 212 Curitiba BRA 1584232 2258 Cali COL 2077386 2485 Casablanca MAR 2940623 2515 Ciudad de México MEX 8591309 3539 Caracas VEN 1975294 3795 Chicago USA 2896016 608 Cairo EGY 6789479 71 Córdoba ARG 1157507 712 Cape Town ZAF 2352121 926 Conakry GIN 1090610 SELECT * FROM City USE INDEX () WHERE Name LIKE 'M%' AND Population > 1500000; ID Name Country Population 1024 Mumbai (Bombay) IND 10500000 131 Melbourne AUS 2865329 1381 Mashhad IRN 1887405 2259 Medellín COL 1861265 3520 Minsk BLR 1674000 3580 Moscow RUS 8389200 653 Madrid ESP 2879052 766 Manila PHL 1581082 942 Medan IDN 1843919 SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 1500000; ID Name Country Population 1024 Mumbai (Bombay) IND 10500000 131 Melbourne AUS 2865329 1381 Mashhad IRN 1887405 2259 Medellín COL 1861265 3520 Minsk BLR 1674000 3580 Moscow RUS 8389200 653 Madrid ESP 2879052 766 Manila PHL 1581082 942 Medan IDN 1843919 SELECT * FROM City USE INDEX () WHERE Name LIKE 'M%' AND Population > 300000; ID Name Country Population 1024 Mumbai (Bombay) IND 10500000 1042 Madurai IND 977856 1051 Meerut IND 753778 1074 Mysore IND 480692 1081 Moradabad IND 429214 1098 Malegaon IND 342595 131 Melbourne AUS 2865329 1366 Mosul IRQ 879000 1381 Mashhad IRN 1887405 1465 Milano ITA 1300977 1559 Matsuyama JPN 466133 1560 Matsudo JPN 461126 1578 Machida JPN 364197 1595 Miyazaki JPN 303784 1810 Montréal CAN 1016376 1816 Mississauga CAN 608072 1882 Mombasa KEN 461753 1945 Mudanjiang CHN 570000 2005 Ma´anshan CHN 305421 215 Manaus BRA 1255049 223 Maceió BRA 786288 2259 Medellín COL 1861265 2267 Manizales COL 337580 2300 Mbuji-Mayi COD 806475 2348 Masan KOR 441242 2440 Monrovia LBR 850000 2454 Macao MAC 437500 2487 Marrakech MAR 621914 2491 Meknès MAR 460000 250 Mauá BRA 375055 2523 Monterrey MEX 1108499 2526 Mexicali MEX 764902 2530 Mérida MEX 703324 2537 Morelia MEX 619958 2554 Matamoros MEX 416428 2557 Mazatlán MEX 380265 256 Moji das Cruzes BRA 339194 2698 Maputo MOZ 1018938 2699 Matola MOZ 424662 2711 Mandalay MMR 885300 2712 Moulmein (Mawlamyine) MMR 307900 2734 Managua NIC 959000 2756 Mushin NGA 333200 2757 Maiduguri NGA 320000 2826 Multan PAK 1182441 2975 Marseille FRA 798430 3070 Munich [München] DEU 1194560 3086 Mannheim DEU 307730 3175 Mekka SAU 965700 3176 Medina SAU 608300 3214 Mogadishu SOM 997000 3364 Mersin (Içel) TUR 587212 3371 Malatya TUR 330312 3434 Mykolajiv UKR 508000 3435 Mariupol UKR 490000 3438 Makijivka UKR 384000 3492 Montevideo URY 1236000 3520 Minsk BLR 1674000 3522 Mogiljov BLR 356000 3540 Maracaíbo VEN 1304776 3545 Maracay VEN 444443 3547 Maturín VEN 319726 3580 Moscow RUS 8389200 3622 Magnitogorsk RUS 427900 3625 Murmansk RUS 376300 3636 Mahat?kala RUS 332800 3810 Memphis USA 650100 3811 Milwaukee USA 596974 3834 Mesa USA 396375 3837 Minneapolis USA 382618 3839 Miami USA 362470 462 Manchester GBR 430000 653 Madrid ESP 2879052 658 Málaga ESP 530553 661 Murcia ESP 353504 766 Manila PHL 1581082 77 Mar del Plata ARG 512880 778 Makati PHL 444867 781 Marikina PHL 391170 783 Muntinlupa PHL 379310 786 Malabon PHL 338855 80 Merlo ARG 463846 83 Moreno ARG 356993 87 Morón ARG 349246 942 Medan IDN 1843919 947 Malang IDN 716862 962 Manado IDN 332288 963 Mataram IDN 306600 SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 300000; ID Name Country Population 1024 Mumbai (Bombay) IND 10500000 1042 Madurai IND 977856 1051 Meerut IND 753778 1074 Mysore IND 480692 1081 Moradabad IND 429214 1098 Malegaon IND 342595 131 Melbourne AUS 2865329 1366 Mosul IRQ 879000 1381 Mashhad IRN 1887405 1465 Milano ITA 1300977 1559 Matsuyama JPN 466133 1560 Matsudo JPN 461126 1578 Machida JPN 364197 1595 Miyazaki JPN 303784 1810 Montréal CAN 1016376 1816 Mississauga CAN 608072 1882 Mombasa KEN 461753 1945 Mudanjiang CHN 570000 2005 Ma´anshan CHN 305421 215 Manaus BRA 1255049 223 Maceió BRA 786288 2259 Medellín COL 1861265 2267 Manizales COL 337580 2300 Mbuji-Mayi COD 806475 2348 Masan KOR 441242 2440 Monrovia LBR 850000 2454 Macao MAC 437500 2487 Marrakech MAR 621914 2491 Meknès MAR 460000 250 Mauá BRA 375055 2523 Monterrey MEX 1108499 2526 Mexicali MEX 764902 2530 Mérida MEX 703324 2537 Morelia MEX 619958 2554 Matamoros MEX 416428 2557 Mazatlán MEX 380265 256 Moji das Cruzes BRA 339194 2698 Maputo MOZ 1018938 2699 Matola MOZ 424662 2711 Mandalay MMR 885300 2712 Moulmein (Mawlamyine) MMR 307900 2734 Managua NIC 959000 2756 Mushin NGA 333200 2757 Maiduguri NGA 320000 2826 Multan PAK 1182441 2975 Marseille FRA 798430 3070 Munich [München] DEU 1194560 3086 Mannheim DEU 307730 3175 Mekka SAU 965700 3176 Medina SAU 608300 3214 Mogadishu SOM 997000 3364 Mersin (Içel) TUR 587212 3371 Malatya TUR 330312 3434 Mykolajiv UKR 508000 3435 Mariupol UKR 490000 3438 Makijivka UKR 384000 3492 Montevideo URY 1236000 3520 Minsk BLR 1674000 3522 Mogiljov BLR 356000 3540 Maracaíbo VEN 1304776 3545 Maracay VEN 444443 3547 Maturín VEN 319726 3580 Moscow RUS 8389200 3622 Magnitogorsk RUS 427900 3625 Murmansk RUS 376300 3636 Mahat?kala RUS 332800 3810 Memphis USA 650100 3811 Milwaukee USA 596974 3834 Mesa USA 396375 3837 Minneapolis USA 382618 3839 Miami USA 362470 462 Manchester GBR 430000 653 Madrid ESP 2879052 658 Málaga ESP 530553 661 Murcia ESP 353504 766 Manila PHL 1581082 77 Mar del Plata ARG 512880 778 Makati PHL 444867 781 Marikina PHL 391170 783 Muntinlupa PHL 379310 786 Malabon PHL 338855 80 Merlo ARG 463846 83 Moreno ARG 356993 87 Morón ARG 349246 942 Medan IDN 1843919 947 Malang IDN 716862 962 Manado IDN 332288 963 Mataram IDN 306600 SELECT * FROM City USE INDEX () WHERE Name LIKE 'M%' AND Population > 7000000; ID Name Country Population 1024 Mumbai (Bombay) IND 10500000 3580 Moscow RUS 8389200 SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 7000000; ID Name Country Population 1024 Mumbai (Bombay) IND 10500000 3580 Moscow RUS 8389200 SELECT COUNT(*) FROM City WHERE Name BETWEEN 'M' AND 'N'; COUNT(*) 301 SELECT COUNT(*) FROM City WHERE Name BETWEEN 'G' AND 'J'; COUNT(*) 408 SELECT COUNT(*) FROM City WHERE Name BETWEEN 'G' AND 'K'; COUNT(*) 512 SELECT COUNT(*) FROM City WHERE Population > 1000000; COUNT(*) 237 SELECT COUNT(*) FROM City WHERE Population > 500000; COUNT(*) 539 SELECT COUNT(*) FROM City WHERE Country LIKE 'C%'; COUNT(*) 551 SELECT COUNT(*) FROM City WHERE Country LIKE 'B%'; COUNT(*) 339 EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Country,Name Population,Name,Country 4,35,3 NULL # Using sort_intersect(Population,Name,Country); Using where EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'J' AND Population > 1000000 AND Country LIKE 'B%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Country,Name Population,Country,Name 4,3,35 NULL # Using sort_intersect(Population,Country,Name); Using where EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Name,Country Name,Population,Country # NULL # Using sort_intersect(Name,Population,Country); Using where SELECT * FROM City USE INDEX () WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%'; ID Name Country Population 1810 Montréal CAN 1016376 2259 Medellín COL 1861265 SELECT * FROM City WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%'; ID Name Country Population 1810 Montréal CAN 1016376 2259 Medellín COL 1861265 SELECT * FROM City USE INDEX () WHERE Name BETWEEN 'G' AND 'J' AND Population > 1000000 AND Country LIKE 'B%'; ID Name Country Population 217 Guarulhos BRA 1095874 218 Goiânia BRA 1056330 SELECT * FROM City WHERE Name BETWEEN 'G' AND 'J' AND Population > 1000000 AND Country LIKE 'B%'; ID Name Country Population 217 Guarulhos BRA 1095874 218 Goiânia BRA 1056330 SELECT * FROM City USE INDEX () WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; ID Name Country Population 1895 Harbin CHN 4289800 1904 Jinan CHN 2278100 1905 Hangzhou CHN 2190500 1914 Guiyang CHN 1465200 1916 Hefei CHN 1369100 1923 Jilin CHN 1040000 1927 Hohhot CHN 916700 1928 Handan CHN 840000 1937 Huainan CHN 700000 1938 Jixi CHN 683885 1944 Jinzhou CHN 570000 1950 Hegang CHN 520000 SELECT * FROM City WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; ID Name Country Population 1895 Harbin CHN 4289800 1904 Jinan CHN 2278100 1905 Hangzhou CHN 2190500 1914 Guiyang CHN 1465200 1916 Hefei CHN 1369100 1923 Jilin CHN 1040000 1927 Hohhot CHN 916700 1928 Handan CHN 840000 1937 Huainan CHN 700000 1938 Jixi CHN 683885 1944 Jinzhou CHN 570000 1950 Hegang CHN 520000 SELECT COUNT(*) FROM City WHERE ID BETWEEN 501 AND 1000; COUNT(*) 500 SELECT COUNT(*) FROM City WHERE ID BETWEEN 1 AND 500; COUNT(*) 500 SELECT COUNT(*) FROM City WHERE ID BETWEEN 2001 AND 2500; COUNT(*) 500 SELECT COUNT(*) FROM City WHERE ID BETWEEN 3701 AND 4000; COUNT(*) 300 SELECT COUNT(*) FROM City WHERE Population > 700000; COUNT(*) 358 SELECT COUNT(*) FROM City WHERE Population > 1000000; COUNT(*) 237 SELECT COUNT(*) FROM City WHERE Population > 300000; COUNT(*) 1062 SELECT COUNT(*) FROM City WHERE Population > 600000; COUNT(*) 428 SELECT COUNT(*) FROM City WHERE Country LIKE 'C%'; COUNT(*) 551 SELECT COUNT(*) FROM City WHERE Country LIKE 'A%'; COUNT(*) 107 SELECT COUNT(*) FROM City WHERE Country LIKE 'H%'; COUNT(*) 22 SELECT COUNT(*) FROM City WHERE Country BETWEEN 'S' AND 'Z'; COUNT(*) 682 EXPLAIN SELECT * FROM City WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,3 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country 4,3 NULL # Using sort_intersect(PRIMARY,Country); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3701 AND 4000 AND Population > 1000000 AND Country BETWEEN 'S' AND 'Z'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z' ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where SELECT * FROM City USE INDEX () WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; ID Name Country Population 554 Santiago de Chile CHL 4703954 SELECT * FROM City WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; ID Name Country Population 554 Santiago de Chile CHL 4703954 SELECT * FROM City USE INDEX () WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%'; ID Name Country Population 1 Kabul AFG 1780000 126 Yerevan ARM 1248700 130 Sydney AUS 3276207 131 Melbourne AUS 2865329 132 Brisbane AUS 1291117 133 Perth AUS 1096829 144 Baku AZE 1787800 56 Luanda AGO 2022000 69 Buenos Aires ARG 2982146 70 La Matanza ARG 1266461 71 Córdoba ARG 1157507 SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%'; ID Name Country Population 1 Kabul AFG 1780000 126 Yerevan ARM 1248700 130 Sydney AUS 3276207 131 Melbourne AUS 2865329 132 Brisbane AUS 1291117 133 Perth AUS 1096829 144 Baku AZE 1787800 56 Luanda AGO 2022000 69 Buenos Aires ARG 2982146 70 La Matanza ARG 1266461 71 Córdoba ARG 1157507 SELECT * FROM City USE INDEX () WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; ID Name Country Population 2409 Zagreb HRV 706770 SELECT * FROM City WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; ID Name Country Population 2409 Zagreb HRV 706770 SELECT * FROM City USE INDEX () WHERE ID BETWEEN 3701 AND 4000 AND Population > 700000 AND Country BETWEEN 'S' AND 'Z'; ID Name Country Population 3769 Ho Chi Minh City VNM 3980000 3770 Hanoi VNM 1410000 3771 Haiphong VNM 783133 3793 New York USA 8008278 3794 Los Angeles USA 3694820 3795 Chicago USA 2896016 3796 Houston USA 1953631 3797 Philadelphia USA 1517550 3798 Phoenix USA 1321045 3799 San Diego USA 1223400 3800 Dallas USA 1188580 3801 San Antonio USA 1144646 3802 Detroit USA 951270 3803 San Jose USA 894943 3804 Indianapolis USA 791926 3805 San Francisco USA 776733 3806 Jacksonville USA 735167 3807 Columbus USA 711470 SELECT * FROM City WHERE ID BETWEEN 3701 AND 4000 AND Population > 700000 AND Country BETWEEN 'S' AND 'Z'; ID Name Country Population 3769 Ho Chi Minh City VNM 3980000 3770 Hanoi VNM 1410000 3771 Haiphong VNM 783133 3793 New York USA 8008278 3794 Los Angeles USA 3694820 3795 Chicago USA 2896016 3796 Houston USA 1953631 3797 Philadelphia USA 1517550 3798 Phoenix USA 1321045 3799 San Diego USA 1223400 3800 Dallas USA 1188580 3801 San Antonio USA 1144646 3802 Detroit USA 951270 3803 San Jose USA 894943 3804 Indianapolis USA 791926 3805 San Francisco USA 776733 3806 Jacksonville USA 735167 3807 Columbus USA 711470 SELECT * FROM City USE INDEX () WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z' ; ID Name Country Population 3048 Stockholm SWE 750348 3173 Riyadh SAU 3324000 3174 Jedda SAU 2046300 3175 Mekka SAU 965700 3176 Medina SAU 608300 3197 Pikine SEN 855287 3198 Dakar SEN 785071 3207 Freetown SLE 850000 3208 Singapore SGP 4017733 3214 Mogadishu SOM 997000 3224 Omdurman SDN 1271403 3225 Khartum SDN 947483 3226 Sharq al-Nil SDN 700887 3250 Damascus SYR 1347000 3251 Aleppo SYR 1261983 3263 Taipei TWN 2641312 3264 Kaohsiung TWN 1475505 3265 Taichung TWN 940589 3266 Tainan TWN 728060 3305 Dar es Salaam TZA 1747000 3320 Bangkok THA 6320174 3349 Tunis TUN 690600 3357 Istanbul TUR 8787958 3358 Ankara TUR 3038159 3359 Izmir TUR 2130359 3360 Adana TUR 1131198 3361 Bursa TUR 1095842 3362 Gaziantep TUR 789056 3363 Konya TUR 628364 3425 Kampala UGA 890800 3426 Kyiv UKR 2624000 3427 Harkova [Harkiv] UKR 1500000 3428 Dnipropetrovsk UKR 1103000 3429 Donetsk UKR 1050000 3430 Odesa UKR 1011000 3431 Zaporizzja UKR 848000 3432 Lviv UKR 788000 3433 Kryvyi Rig UKR 703000 3492 Montevideo URY 1236000 3503 Toskent UZB 2117500 3539 Caracas VEN 1975294 3540 Maracaíbo VEN 1304776 3541 Barquisimeto VEN 877239 3542 Valencia VEN 794246 3543 Ciudad Guayana VEN 663713 3769 Ho Chi Minh City VNM 3980000 3770 Hanoi VNM 1410000 3771 Haiphong VNM 783133 3793 New York USA 8008278 3794 Los Angeles USA 3694820 3795 Chicago USA 2896016 3796 Houston USA 1953631 3797 Philadelphia USA 1517550 3798 Phoenix USA 1321045 3799 San Diego USA 1223400 3800 Dallas USA 1188580 3801 San Antonio USA 1144646 3802 Detroit USA 951270 3803 San Jose USA 894943 3804 Indianapolis USA 791926 3805 San Francisco USA 776733 3806 Jacksonville USA 735167 3807 Columbus USA 711470 3808 Austin USA 656562 3809 Baltimore USA 651154 3810 Memphis USA 650100 SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z' ; ID Name Country Population 3048 Stockholm SWE 750348 3173 Riyadh SAU 3324000 3174 Jedda SAU 2046300 3175 Mekka SAU 965700 3176 Medina SAU 608300 3197 Pikine SEN 855287 3198 Dakar SEN 785071 3207 Freetown SLE 850000 3208 Singapore SGP 4017733 3214 Mogadishu SOM 997000 3224 Omdurman SDN 1271403 3225 Khartum SDN 947483 3226 Sharq al-Nil SDN 700887 3250 Damascus SYR 1347000 3251 Aleppo SYR 1261983 3263 Taipei TWN 2641312 3264 Kaohsiung TWN 1475505 3265 Taichung TWN 940589 3266 Tainan TWN 728060 3305 Dar es Salaam TZA 1747000 3320 Bangkok THA 6320174 3349 Tunis TUN 690600 3357 Istanbul TUR 8787958 3358 Ankara TUR 3038159 3359 Izmir TUR 2130359 3360 Adana TUR 1131198 3361 Bursa TUR 1095842 3362 Gaziantep TUR 789056 3363 Konya TUR 628364 3425 Kampala UGA 890800 3426 Kyiv UKR 2624000 3427 Harkova [Harkiv] UKR 1500000 3428 Dnipropetrovsk UKR 1103000 3429 Donetsk UKR 1050000 3430 Odesa UKR 1011000 3431 Zaporizzja UKR 848000 3432 Lviv UKR 788000 3433 Kryvyi Rig UKR 703000 3492 Montevideo URY 1236000 3503 Toskent UZB 2117500 3539 Caracas VEN 1975294 3540 Maracaíbo VEN 1304776 3541 Barquisimeto VEN 877239 3542 Valencia VEN 794246 3543 Ciudad Guayana VEN 663713 3769 Ho Chi Minh City VNM 3980000 3770 Hanoi VNM 1410000 3771 Haiphong VNM 783133 3793 New York USA 8008278 3794 Los Angeles USA 3694820 3795 Chicago USA 2896016 3796 Houston USA 1953631 3797 Philadelphia USA 1517550 3798 Phoenix USA 1321045 3799 San Diego USA 1223400 3800 Dallas USA 1188580 3801 San Antonio USA 1144646 3802 Detroit USA 951270 3803 San Jose USA 894943 3804 Indianapolis USA 791926 3805 San Francisco USA 776733 3806 Jacksonville USA 735167 3807 Columbus USA 711470 3808 Austin USA 656562 3809 Baltimore USA 651154 3810 Memphis USA 650100 SET SESSION sort_buffer_size = 2048; EXPLAIN SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where EXPLAIN SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 1500000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'J' AND Population > 1000000 AND Country LIKE 'B%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Country,Name Population,Country,Name 4,3,35 NULL # Using sort_intersect(Population,Country,Name); Using where EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Country,Name Name,Population,Country 35,4,3 NULL # Using sort_intersect(Name,Population,Country); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,3 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; ID Name Country Population 1026 Calcutta [Kolkata] IND 4399819 1027 Chennai (Madras) IND 3841396 151 Chittagong BGD 1392860 1892 Chongqing CHN 6351600 1898 Chengdu CHN 3361500 1900 Changchun CHN 2812000 1910 Changsha CHN 1809800 212 Curitiba BRA 1584232 2258 Cali COL 2077386 2485 Casablanca MAR 2940623 2515 Ciudad de México MEX 8591309 3539 Caracas VEN 1975294 3795 Chicago USA 2896016 608 Cairo EGY 6789479 71 Córdoba ARG 1157507 712 Cape Town ZAF 2352121 926 Conakry GIN 1090610 SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 1500000; ID Name Country Population 1024 Mumbai (Bombay) IND 10500000 131 Melbourne AUS 2865329 1381 Mashhad IRN 1887405 2259 Medellín COL 1861265 3520 Minsk BLR 1674000 3580 Moscow RUS 8389200 653 Madrid ESP 2879052 766 Manila PHL 1581082 942 Medan IDN 1843919 SELECT * FROM City WHERE Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'B%'; ID Name Country Population 217 Guarulhos BRA 1095874 218 Goiânia BRA 1056330 SELECT * FROM City WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%'; ID Name Country Population 1895 Harbin CHN 4289800 1905 Hangzhou CHN 2190500 1914 Guiyang CHN 1465200 1916 Hefei CHN 1369100 1927 Hohhot CHN 916700 1928 Handan CHN 840000 1937 Huainan CHN 700000 1950 Hegang CHN 520000 SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%'; ID Name Country Population 1 Kabul AFG 1780000 56 Luanda AGO 2022000 69 Buenos Aires ARG 2982146 70 La Matanza ARG 1266461 71 Córdoba ARG 1157507 126 Yerevan ARM 1248700 130 Sydney AUS 3276207 131 Melbourne AUS 2865329 132 Brisbane AUS 1291117 133 Perth AUS 1096829 144 Baku AZE 1787800 SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z'; ID Name Country Population 3048 Stockholm SWE 750348 3173 Riyadh SAU 3324000 3174 Jedda SAU 2046300 3175 Mekka SAU 965700 3176 Medina SAU 608300 3197 Pikine SEN 855287 3198 Dakar SEN 785071 3207 Freetown SLE 850000 3208 Singapore SGP 4017733 3214 Mogadishu SOM 997000 3224 Omdurman SDN 1271403 3225 Khartum SDN 947483 3226 Sharq al-Nil SDN 700887 3250 Damascus SYR 1347000 3251 Aleppo SYR 1261983 3263 Taipei TWN 2641312 3264 Kaohsiung TWN 1475505 3265 Taichung TWN 940589 3266 Tainan TWN 728060 3305 Dar es Salaam TZA 1747000 3320 Bangkok THA 6320174 3349 Tunis TUN 690600 3357 Istanbul TUR 8787958 3358 Ankara TUR 3038159 3359 Izmir TUR 2130359 3360 Adana TUR 1131198 3361 Bursa TUR 1095842 3362 Gaziantep TUR 789056 3363 Konya TUR 628364 3425 Kampala UGA 890800 3426 Kyiv UKR 2624000 3427 Harkova [Harkiv] UKR 1500000 3428 Dnipropetrovsk UKR 1103000 3429 Donetsk UKR 1050000 3430 Odesa UKR 1011000 3431 Zaporizzja UKR 848000 3432 Lviv UKR 788000 3433 Kryvyi Rig UKR 703000 3492 Montevideo URY 1236000 3503 Toskent UZB 2117500 3539 Caracas VEN 1975294 3540 Maracaíbo VEN 1304776 3541 Barquisimeto VEN 877239 3542 Valencia VEN 794246 3543 Ciudad Guayana VEN 663713 3769 Ho Chi Minh City VNM 3980000 3770 Hanoi VNM 1410000 3771 Haiphong VNM 783133 3793 New York USA 8008278 3794 Los Angeles USA 3694820 3795 Chicago USA 2896016 3796 Houston USA 1953631 3797 Philadelphia USA 1517550 3798 Phoenix USA 1321045 3799 San Diego USA 1223400 3800 Dallas USA 1188580 3801 San Antonio USA 1144646 3802 Detroit USA 951270 3803 San Jose USA 894943 3804 Indianapolis USA 791926 3805 San Francisco USA 776733 3806 Jacksonville USA 735167 3807 Columbus USA 711470 3808 Austin USA 656562 3809 Baltimore USA 651154 3810 Memphis USA 650100 SET SESSION sort_buffer_size = default; DROP INDEX Country ON City; CREATE INDEX CountryID ON City(Country,ID); CREATE INDEX CountryName ON City(Country,Name); EXPLAIN SELECT * FROM City WHERE Country LIKE 'M%' AND Population > 1000000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,CountryID,CountryName Population,CountryID 4,3 NULL # Using sort_intersect(Population,CountryID); Using where EXPLAIN SELECT * FROM City WHERE Country='CHN' AND Population > 1500000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,CountryID,CountryName Population,CountryID 4,3 NULL # Using sort_intersect(Population,CountryID); Using where EXPLAIN SELECT * FROM City WHERE Country='CHN' AND Population > 1500000 AND Name LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Name,CountryID,CountryName CountryName,Population 38,4 NULL # Using sort_intersect(CountryName,Population); Using where SELECT * FROM City USE INDEX () WHERE Country LIKE 'M%' AND Population > 1000000; ID Name Country Population 2464 Kuala Lumpur MYS 1297526 2485 Casablanca MAR 2940623 2515 Ciudad de México MEX 8591309 2516 Guadalajara MEX 1647720 2517 Ecatepec de Morelos MEX 1620303 2518 Puebla MEX 1346176 2519 Nezahualcóyotl MEX 1224924 2520 Juárez MEX 1217818 2521 Tijuana MEX 1212232 2522 León MEX 1133576 2523 Monterrey MEX 1108499 2524 Zapopan MEX 1002239 2698 Maputo MOZ 1018938 2710 Rangoon (Yangon) MMR 3361700 SELECT * FROM City WHERE Country LIKE 'M%' AND Population > 1000000; ID Name Country Population 2464 Kuala Lumpur MYS 1297526 2485 Casablanca MAR 2940623 2515 Ciudad de México MEX 8591309 2516 Guadalajara MEX 1647720 2517 Ecatepec de Morelos MEX 1620303 2518 Puebla MEX 1346176 2519 Nezahualcóyotl MEX 1224924 2520 Juárez MEX 1217818 2521 Tijuana MEX 1212232 2522 León MEX 1133576 2523 Monterrey MEX 1108499 2524 Zapopan MEX 1002239 2698 Maputo MOZ 1018938 2710 Rangoon (Yangon) MMR 3361700 SELECT * FROM City USE INDEX () WHERE Country='CHN' AND Population > 1500000; ID Name Country Population 1890 Shanghai CHN 9696300 1891 Peking CHN 7472000 1892 Chongqing CHN 6351600 1893 Tianjin CHN 5286800 1894 Wuhan CHN 4344600 1895 Harbin CHN 4289800 1896 Shenyang CHN 4265200 1897 Kanton [Guangzhou] CHN 4256300 1898 Chengdu CHN 3361500 1899 Nanking [Nanjing] CHN 2870300 1900 Changchun CHN 2812000 1901 Xi´an CHN 2761400 1902 Dalian CHN 2697000 1903 Qingdao CHN 2596000 1904 Jinan CHN 2278100 1905 Hangzhou CHN 2190500 1906 Zhengzhou CHN 2107200 1907 Shijiazhuang CHN 2041500 1908 Taiyuan CHN 1968400 1909 Kunming CHN 1829500 1910 Changsha CHN 1809800 1911 Nanchang CHN 1691600 1912 Fuzhou CHN 1593800 1913 Lanzhou CHN 1565800 SELECT * FROM City WHERE Country='CHN' AND Population > 1500000; ID Name Country Population 1890 Shanghai CHN 9696300 1891 Peking CHN 7472000 1892 Chongqing CHN 6351600 1893 Tianjin CHN 5286800 1894 Wuhan CHN 4344600 1895 Harbin CHN 4289800 1896 Shenyang CHN 4265200 1897 Kanton [Guangzhou] CHN 4256300 1898 Chengdu CHN 3361500 1899 Nanking [Nanjing] CHN 2870300 1900 Changchun CHN 2812000 1901 Xi´an CHN 2761400 1902 Dalian CHN 2697000 1903 Qingdao CHN 2596000 1904 Jinan CHN 2278100 1905 Hangzhou CHN 2190500 1906 Zhengzhou CHN 2107200 1907 Shijiazhuang CHN 2041500 1908 Taiyuan CHN 1968400 1909 Kunming CHN 1829500 1910 Changsha CHN 1809800 1911 Nanchang CHN 1691600 1912 Fuzhou CHN 1593800 1913 Lanzhou CHN 1565800 SELECT * FROM City USE INDEX () WHERE Country='CHN' AND Population > 1500000 AND Name LIKE 'C%'; ID Name Country Population 1892 Chongqing CHN 6351600 1898 Chengdu CHN 3361500 1900 Changchun CHN 2812000 1910 Changsha CHN 1809800 SELECT * FROM City WHERE Country='CHN' AND Population > 1500000 AND Name LIKE 'C%'; ID Name Country Population 1892 Chongqing CHN 6351600 1898 Chengdu CHN 3361500 1900 Changchun CHN 2812000 1910 Changsha CHN 1809800 EXPLAIN SELECT * FROM City, Country WHERE City.Name LIKE 'C%' AND City.Population > 1000000 AND Country.Code=City.Country; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Name,CountryID,CountryName Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where 1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.City.Country # DROP DATABASE world; use test; CREATE TABLE t1 ( f1 int, f4 varchar(32), f5 int, PRIMARY KEY (f1), KEY (f4) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (5,'H',1), (9,'g',0), (527,'i',0), (528,'y',1), (529,'S',6), (530,'m',7), (531,'b',2), (532,'N',1), (533,'V',NULL), (534,'l',1), (535,'M',0), (536,'w',1), (537,'j',5), (538,'l',0), (539,'n',2), (540,'m',2), (541,'r',2), (542,'l',2), (543,'h',3),(544,'o',0), (956,'h',0), (957,'g',0), (958,'W',5), (959,'s',3), (960,'w',0), (961,'q',0), (962,'e',NULL), (963,'u',7), (964,'q',1), (965,'N',NULL), (966,'e',0), (967,'t',3), (968,'e',6), (969,'f',NULL), (970,'j',0), (971,'s',3), (972,'I',0), (973,'h',4), (974,'g',1), (975,'s',0), (976,'r',3), (977,'x',1), (978,'v',8), (979,'j',NULL), (980,'z',7), (981,'t',9), (982,'j',5), (983,'u',NULL), (984,'g',6), (985,'w',1), (986,'h',1), (987,'v',0), (988,'v',0), (989,'c',2), (990,'b',7), (991,'z',0), (992,'M',1), (993,'u',2), (994,'r',2), (995,'b',4), (996,'A',2), (997,'u',0), (998,'a',0), (999,'j',2), (1,'I',2); EXPLAIN SELECT * FROM t1 WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge PRIMARY,f4 PRIMARY,f4 4,35 NULL # Using sort_intersect(PRIMARY,f4); Using where SELECT * FROM t1 WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; f1 f4 f5 994 r 2 996 A 2 998 a 0 DROP TABLE t1; SET SESSION optimizer_switch='index_merge_sort_intersection=on'; SET SESSION STORAGE_ENGINE=DEFAULT;