diff options
Diffstat (limited to 'mysql-test/r/index_intersect.result')
| -rw-r--r-- | mysql-test/r/index_intersect.result | 1043 |
1 files changed, 0 insertions, 1043 deletions
diff --git a/mysql-test/r/index_intersect.result b/mysql-test/r/index_intersect.result deleted file mode 100644 index 7a0633d4dc8..00000000000 --- a/mysql-test/r/index_intersect.result +++ /dev/null @@ -1,1043 +0,0 @@ -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 Name,Population 35,4 NULL # Using sort_intersect(Name,Population); 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 range Population,Name Name 35 NULL # Using index condition; 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 range Population,Name Population 4 NULL # Using index condition; 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 -3580 Moscow RUS 8389200 -1024 Mumbai (Bombay) IND 10500000 -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 Name,Population 35,4 NULL # Using sort_intersect(Name,Population); 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 4,3 NULL # Using sort_intersect(Population,Country); 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 range Population,Name,Country Name # NULL # Using index condition; 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 range PRIMARY,Population,Country Population 4 NULL # Using index condition; 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 Country,Population 3,4 NULL # Using sort_intersect(Country,Population); 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 range PRIMARY,Population,Country Country 3 NULL # Using index condition; 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 Population,PRIMARY 4,4 NULL # Using sort_intersect(Population,PRIMARY); 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 range PRIMARY,Population,Country Population 4 NULL # Using index condition; 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 Name,Population 35,4 NULL # Using sort_intersect(Name,Population); 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 4,3 NULL # Using sort_intersect(Population,Country); 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 range Population,Country,Name Name 35 NULL # Using index condition; 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 Country,Population 3,4 NULL # Using sort_intersect(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 range PRIMARY,Population,Country Population 4 NULL # Using index condition; 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 Name,Population 35,4 NULL # Using sort_intersect(Name,Population); 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) -); -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 range PRIMARY,f4 f4 35 NULL # Using index condition; 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'; |