summaryrefslogtreecommitdiff
path: root/mysql-test/r/index_intersect.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/index_intersect.result')
-rw-r--r--mysql-test/r/index_intersect.result1046
1 files changed, 1046 insertions, 0 deletions
diff --git a/mysql-test/r/index_intersect.result b/mysql-test/r/index_intersect.result
new file mode 100644
index 00000000000..3c3c892851e
--- /dev/null
+++ b/mysql-test/r/index_intersect.result
@@ -0,0 +1,1046 @@
+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 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 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 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
+1914 Guiyang CHN 1465200
+1928 Handan CHN 840000
+1905 Hangzhou CHN 2190500
+1895 Harbin CHN 4289800
+1916 Hefei CHN 1369100
+1950 Hegang CHN 520000
+1927 Hohhot CHN 916700
+1937 Huainan CHN 700000
+1923 Jilin CHN 1040000
+1904 Jinan CHN 2278100
+1944 Jinzhou CHN 570000
+1938 Jixi CHN 683885
+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 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 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 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 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 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
+131 Melbourne AUS 2865329
+653 Madrid ESP 2879052
+766 Manila PHL 1581082
+942 Medan IDN 1843919
+1024 Mumbai (Bombay) IND 10500000
+1381 Mashhad IRN 1887405
+2259 Medellín COL 1861265
+3520 Minsk BLR 1674000
+3580 Moscow RUS 8389200
+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
+1914 Guiyang CHN 1465200
+1928 Handan CHN 840000
+1905 Hangzhou CHN 2190500
+1895 Harbin CHN 4289800
+1916 Hefei CHN 1369100
+1950 Hegang CHN 520000
+1927 Hohhot CHN 916700
+1937 Huainan CHN 700000
+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)
+) ENGINE=InnoDB;
+Warnings:
+Warning 1286 Unknown storage engine 'InnoDB'
+Warning 1266 Using storage engine MyISAM for table 't1'
+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 where
+SELECT * FROM t1
+WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ;
+f1 f4 f5
+996 A 2
+998 a 0
+994 r 2
+DROP TABLE t1;
+SET SESSION optimizer_switch='index_merge_sort_intersection=on';