diff options
Diffstat (limited to 'mysql-test/main/index_intersect.result')
| -rw-r--r-- | mysql-test/main/index_intersect.result | 1043 |
1 files changed, 1043 insertions, 0 deletions
diff --git a/mysql-test/main/index_intersect.result b/mysql-test/main/index_intersect.result new file mode 100644 index 00000000000..7a0633d4dc8 --- /dev/null +++ b/mysql-test/main/index_intersect.result @@ -0,0 +1,1043 @@ +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'; |