1DROP TABLE IF EXISTS t1,t2,t3,t4; 2DROP DATABASE IF EXISTS world; 3set names utf8; 4CREATE DATABASE world; 5use world; 6CREATE TABLE Country ( 7Code char(3) NOT NULL default '', 8Name char(52) NOT NULL default '', 9SurfaceArea float(10,2) NOT NULL default '0.00', 10Population int(11) NOT NULL default '0', 11Capital int(11) default NULL, 12PRIMARY KEY (Code), 13UNIQUE INDEX (Name) 14); 15CREATE TABLE City ( 16ID int(11) NOT NULL auto_increment, 17Name char(35) NOT NULL default '', 18Country char(3) NOT NULL default '', 19Population int(11) NOT NULL default '0', 20PRIMARY KEY (ID), 21INDEX (Population), 22INDEX (Country) 23); 24CREATE TABLE CountryLanguage ( 25Country char(3) NOT NULL default '', 26Language char(30) NOT NULL default '', 27Percentage float(3,1) NOT NULL default '0.0', 28PRIMARY KEY (Country, Language), 29INDEX (Percentage) 30); 31SELECT COUNT(*) FROM Country; 32COUNT(*) 33239 34SELECT COUNT(*) FROM City; 35COUNT(*) 364079 37SELECT COUNT(*) FROM CountryLanguage; 38COUNT(*) 39984 40CREATE INDEX Name ON City(Name); 41set session optimizer_switch='index_merge_sort_intersection=off'; 42EXPLAIN 43SELECT * FROM City 44WHERE (Population >= 100000 OR Name LIKE 'P%' OR Population < 100000); 45id select_type table type possible_keys key key_len ref rows Extra 461 SIMPLE City ALL Population,Name NULL NULL NULL 4079 Using where 47EXPLAIN 48SELECT * FROM City 49WHERE (Population >= 100000 OR Name LIKE 'P%') AND Country='CAN' OR 50(Population < 100000 OR Name Like 'T%') AND Country='ARG'; 51id select_type table type possible_keys key key_len ref rows Extra 521 SIMPLE City range Population,Country,Name Country 3 NULL 104 Using index condition; Using where 53EXPLAIN 54SELECT * FROM City 55WHERE Population < 200000 AND Name LIKE 'P%' AND 56(Population > 300000 OR Name LIKE 'T%') AND 57(Population < 100000 OR Name LIKE 'Pa%'); 58id select_type table type possible_keys key key_len ref rows Extra 591 SIMPLE City range Population,Name Name 35 NULL 135 Using index condition; Using where 60EXPLAIN 61SELECT * FROM City 62WHERE Population > 100000 AND Name LIKE 'Aba%' OR 63Country IN ('CAN', 'ARG') AND ID BETWEEN 120 AND 130 OR 64Country <= 'ALB' AND Name LIKE 'L%' OR 65ID BETWEEN 3807 AND 3810; 66id select_type table type possible_keys key key_len ref rows Extra 671 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,PRIMARY,Country 35,4,3 NULL 30 Using sort_union(Name,PRIMARY,Country); Using where 68EXPLAIN 69SELECT * FROM City 70WHERE (Population > 101000 AND Population < 115000); 71id select_type table type possible_keys key key_len ref rows Extra 721 SIMPLE City range Population Population 4 NULL 459 Using index condition 73EXPLAIN 74SELECT * FROM City 75WHERE (Population > 101000 AND Population < 102000); 76id select_type table type possible_keys key key_len ref rows Extra 771 SIMPLE City range Population Population 4 NULL 39 Using index condition 78EXPLAIN 79SELECT * FROM City 80WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')); 81id select_type table type possible_keys key key_len ref rows Extra 821 SIMPLE City index_merge Country,Name Name,Country 35,3 NULL 172 Using sort_union(Name,Country); Using where 83EXPLAIN 84SELECT * FROM City 85WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) 86AND (Population > 101000 AND Population < 115000); 87id select_type table type possible_keys key key_len ref rows Extra 881 SIMPLE City index_merge Population,Country,Name Name,Country 35,3 NULL 172 Using sort_union(Name,Country); Using where 89EXPLAIN 90SELECT * FROM City 91WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) 92AND (Population > 101000 AND Population < 102000); 93id select_type table type possible_keys key key_len ref rows Extra 941 SIMPLE City range Population,Country,Name Population 4 NULL 39 Using index condition; Using where 95SELECT * FROM City USE INDEX () 96WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) 97AND (Population > 101000 AND Population < 115000); 98ID Name Country Population 99403 Catanduva BRA 107761 100412 Cachoeirinha BRA 103240 101636 Bilbays EGY 113608 102637 Mit Ghamr EGY 101801 103701 Tarragona ESP 113016 104702 Lleida (Lérida) ESP 112207 105703 Jaén ESP 109247 106704 Ourense (Orense) ESP 109120 107705 Mataró ESP 104095 108706 Algeciras ESP 103106 109707 Marbella ESP 101144 110759 Gonder ETH 112249 111869 Cabuyao PHL 106630 112870 Calapan PHL 105910 113873 Cauayan PHL 103952 1141844 Cape Breton CAN 114733 1151847 Cambridge CAN 109186 1162908 Cajamarca PER 108009 1173003 Caen FRA 113987 1183411 Ceyhan TUR 102412 1193571 Calabozo VEN 107146 1203786 Cam Ranh VNM 114041 1213792 Tartu EST 101246 1224002 Carrollton USA 109576 1234027 Cape Coral USA 102286 1244032 Cambridge USA 101355 125SELECT * FROM City 126WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) 127AND (Population > 101000 AND Population < 115000); 128ID Name Country Population 129403 Catanduva BRA 107761 130412 Cachoeirinha BRA 103240 131636 Bilbays EGY 113608 132637 Mit Ghamr EGY 101801 133701 Tarragona ESP 113016 134702 Lleida (Lérida) ESP 112207 135703 Jaén ESP 109247 136704 Ourense (Orense) ESP 109120 137705 Mataró ESP 104095 138706 Algeciras ESP 103106 139707 Marbella ESP 101144 140759 Gonder ETH 112249 141869 Cabuyao PHL 106630 142870 Calapan PHL 105910 143873 Cauayan PHL 103952 1441844 Cape Breton CAN 114733 1451847 Cambridge CAN 109186 1462908 Cajamarca PER 108009 1473003 Caen FRA 113987 1483411 Ceyhan TUR 102412 1493571 Calabozo VEN 107146 1503786 Cam Ranh VNM 114041 1513792 Tartu EST 101246 1524002 Carrollton USA 109576 1534027 Cape Coral USA 102286 1544032 Cambridge USA 101355 155SELECT * FROM City USE INDEX () 156WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) 157AND (Population > 101000 AND Population < 102000); 158ID Name Country Population 159637 Mit Ghamr EGY 101801 160707 Marbella ESP 101144 1613792 Tartu EST 101246 1624032 Cambridge USA 101355 163SELECT * FROM City 164WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) 165AND (Population > 101000 AND Population < 102000); 166ID Name Country Population 167707 Marbella ESP 101144 1683792 Tartu EST 101246 1694032 Cambridge USA 101355 170637 Mit Ghamr EGY 101801 171EXPLAIN 172SELECT * FROM City WHERE (Name < 'Ac'); 173id select_type table type possible_keys key key_len ref rows Extra 1741 SIMPLE City range Name Name 35 NULL 13 Using index condition 175EXPLAIN 176SELECT * FROM City WHERE (Name < 'Bb'); 177id select_type table type possible_keys key key_len ref rows Extra 1781 SIMPLE City range Name Name 35 NULL 207 Using index condition 179EXPLAIN 180SELECT * FROM City WHERE (Country > 'A' AND Country < 'B'); 181id select_type table type possible_keys key key_len ref rows Extra 1821 SIMPLE City range Country Country 3 NULL 104 Using index condition 183EXPLAIN 184SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'Pb'); 185id select_type table type possible_keys key key_len ref rows Extra 1861 SIMPLE City range Name Name 35 NULL 39 Using index condition 187EXPLAIN 188SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'S'); 189id select_type table type possible_keys key key_len ref rows Extra 1901 SIMPLE City range Name Name 35 NULL 221 Using index condition 191EXPLAIN 192SELECT * FROM City WHERE (Population > 101000 AND Population < 110000); 193id select_type table type possible_keys key key_len ref rows Extra 1941 SIMPLE City range Population Population 4 NULL 328 Using index condition 195EXPLAIN 196SELECT * FROM City WHERE (Population > 103000 AND Population < 104000); 197id select_type table type possible_keys key key_len ref rows Extra 1981 SIMPLE City range Population Population 4 NULL 37 Using index condition 199EXPLAIN 200SELECT * FROM City 201WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR 202(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); 203id select_type table type possible_keys key key_len ref rows Extra 2041 SIMPLE City range Population,Country,Name Name 35 NULL 52 Using index condition; Using where 205EXPLAIN 206SELECT * FROM City 207WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR 208(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); 209id select_type table type possible_keys key key_len ref rows Extra 2101 SIMPLE City index_merge Population,Country,Name Name,Population 35,4 NULL 50 Using sort_union(Name,Population); Using where 211EXPLAIN 212SELECT * FROM City 213WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR 214(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); 215id select_type table type possible_keys key key_len ref rows Extra 2161 SIMPLE City index_merge Population,Country,Name Country,Name 3,35 NULL 143 Using sort_union(Country,Name); Using where 217EXPLAIN 218SELECT * FROM City 219WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR 220(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); 221id select_type table type possible_keys key key_len ref rows Extra 2221 SIMPLE City index_merge Population,Country,Name Country,Population 3,4 NULL 141 Using sort_union(Country,Population); Using where 223SELECT * FROM City USE INDEX () 224WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR 225(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); 226ID Name Country Population 22765 Abu Dhabi ARE 398695 228168 Pabna BGD 103277 229189 Parakou BEN 103577 230750 Paarl ZAF 105768 2312865 Pak Pattan PAK 107800 232SELECT * FROM City 233WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR 234(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); 235ID Name Country Population 23665 Abu Dhabi ARE 398695 237750 Paarl ZAF 105768 238168 Pabna BGD 103277 2392865 Pak Pattan PAK 107800 240189 Parakou BEN 103577 241SELECT * FROM City USE INDEX () 242WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR 243(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); 244ID Name Country Population 24565 Abu Dhabi ARE 398695 246168 Pabna BGD 103277 247189 Parakou BEN 103577 2481003 Pemalang IDN 103500 2492663 Río Bravo MEX 103901 250SELECT * FROM City 251WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR 252(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); 253ID Name Country Population 25465 Abu Dhabi ARE 398695 255168 Pabna BGD 103277 256189 Parakou BEN 103577 2571003 Pemalang IDN 103500 2582663 Río Bravo MEX 103901 259SELECT * FROM City USE INDEX () 260WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR 261(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); 262ID Name Country Population 26355 Andorra la Vella AND 21189 26465 Abu Dhabi ARE 398695 26567 al-Ayn ARE 225970 26668 Ajman ARE 114395 26775 Almirante Brown ARG 538918 26885 Avellaneda ARG 353046 26996 Bahía Blanca ARG 239810 270134 Adelaide AUS 978100 271144 Baku AZE 1787800 272168 Pabna BGD 103277 273189 Parakou BEN 103577 274750 Paarl ZAF 105768 2752865 Pak Pattan PAK 107800 276SELECT * FROM City 277WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR 278(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); 279ID Name Country Population 28055 Andorra la Vella AND 21189 28165 Abu Dhabi ARE 398695 28267 al-Ayn ARE 225970 28368 Ajman ARE 114395 28475 Almirante Brown ARG 538918 28585 Avellaneda ARG 353046 28696 Bahía Blanca ARG 239810 287134 Adelaide AUS 978100 288144 Baku AZE 1787800 289168 Pabna BGD 103277 290189 Parakou BEN 103577 291750 Paarl ZAF 105768 2922865 Pak Pattan PAK 107800 293SELECT * FROM City USE INDEX () 294WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR 295(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); 296ID Name Country Population 29755 Andorra la Vella AND 21189 29865 Abu Dhabi ARE 398695 29967 al-Ayn ARE 225970 30068 Ajman ARE 114395 30175 Almirante Brown ARG 538918 30285 Avellaneda ARG 353046 30396 Bahía Blanca ARG 239810 304134 Adelaide AUS 978100 305144 Baku AZE 1787800 306168 Pabna BGD 103277 307189 Parakou BEN 103577 3081003 Pemalang IDN 103500 3092663 Río Bravo MEX 103901 310SELECT * FROM City 311WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR 312(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); 313ID Name Country Population 31455 Andorra la Vella AND 21189 31565 Abu Dhabi ARE 398695 31667 al-Ayn ARE 225970 31768 Ajman ARE 114395 31875 Almirante Brown ARG 538918 31985 Avellaneda ARG 353046 32096 Bahía Blanca ARG 239810 321134 Adelaide AUS 978100 322144 Baku AZE 1787800 323168 Pabna BGD 103277 324189 Parakou BEN 103577 3251003 Pemalang IDN 103500 3262663 Río Bravo MEX 103901 327EXPLAIN 328SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110); 329id select_type table type possible_keys key key_len ref rows Extra 3301 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using index condition 331EXPLAIN 332SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200); 333id select_type table type possible_keys key key_len ref rows Extra 3341 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using index condition 335EXPLAIN 336SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500); 337id select_type table type possible_keys key key_len ref rows Extra 3381 SIMPLE City ALL PRIMARY NULL NULL NULL 4079 Using where 339EXPLAIN 340SELECT * FROM City WHERE Country > 'A' AND Country < 'ARG'; 341id select_type table type possible_keys key key_len ref rows Extra 3421 SIMPLE City range Country Country 3 NULL 20 Using index condition 343EXPLAIN 344SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ; 345id select_type table type possible_keys key key_len ref rows Extra 3461 SIMPLE City range Name Name 35 NULL 223 Using index condition 347EXPLAIN 348SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ; 349id select_type table type possible_keys key key_len ref rows Extra 3501 SIMPLE City range Name Name 35 NULL 72 Using index condition 351EXPLAIN 352SELECT * FROM City 353WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) 354OR ((ID BETWEEN 100 AND 110) AND 355(Name LIKE 'P%' OR (Population > 103000 AND Population < 104000))); 356id select_type table type possible_keys key key_len ref rows Extra 3571 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 20 Using index condition; Using where 358EXPLAIN 359SELECT * FROM City 360WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) 361OR ((ID BETWEEN 900 AND 1500) AND 362(Name LIKE 'Pa%' OR (Population > 103000 AND Population < 105000))); 363id select_type table type possible_keys key key_len ref rows Extra 3641 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,Population 35,3,4 NULL 151 Using sort_union(Name,Country,Population); Using where 365EXPLAIN 366SELECT * FROM City 367WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) 368OR ((ID BETWEEN 100 AND 200) AND 369(Name LIKE 'Pa%' OR (Population > 103200 AND Population < 104000))); 370id select_type table type possible_keys key key_len ref rows Extra 3711 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,Population 35,3,4 NULL 124 Using sort_union(Name,Country,Population); Using where 372SELECT * FROM City USE INDEX () 373WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) 374OR ((ID BETWEEN 100 AND 110) AND 375(Name LIKE 'P%' OR (Population > 103000 AND Population < 104000))); 376ID Name Country Population 3771 Kabul AFG 1780000 3782 Qandahar AFG 237500 3793 Herat AFG 186800 3804 Mazar-e-Sharif AFG 127800 3817 Haag NLD 440900 382100 Paraná ARG 207041 383102 Posadas ARG 201273 384SELECT * FROM City 385WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) 386OR ((ID BETWEEN 100 AND 110) AND 387(Name LIKE 'P%' OR (Population > 103000 AND Population < 104000))); 388ID Name Country Population 3891 Kabul AFG 1780000 3902 Qandahar AFG 237500 3913 Herat AFG 186800 3924 Mazar-e-Sharif AFG 127800 3937 Haag NLD 440900 394100 Paraná ARG 207041 395102 Posadas ARG 201273 396SELECT * FROM City USE INDEX() 397WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) 398OR ((ID BETWEEN 900 AND 1500) AND 399(Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); 400ID Name Country Population 4011 Kabul AFG 1780000 4022 Qandahar AFG 237500 4033 Herat AFG 186800 4044 Mazar-e-Sharif AFG 127800 4057 Haag NLD 440900 40616 Haarlem NLD 148772 40725 Haarlemmermeer NLD 110722 40833 Willemstad ANT 2345 40934 Tirana ALB 270000 41055 Andorra la Vella AND 21189 41156 Luanda AGO 2022000 41257 Huambo AGO 163100 41358 Lobito AGO 130000 41459 Benguela AGO 128300 41560 Namibe AGO 118200 41661 South Hill AIA 961 41762 The Valley AIA 595 41864 Dubai ARE 669181 41965 Abu Dhabi ARE 398695 42066 Sharja ARE 320095 42167 al-Ayn ARE 225970 42268 Ajman ARE 114395 423129 Oranjestad ABW 29034 424191 Hamilton BMU 1200 425528 Hartlepool GBR 92000 426529 Halifax GBR 91069 427914 Sekondi-Takoradi GHA 103653 428943 Palembang IDN 1222764 429950 Padang IDN 534474 430983 Palu IDN 142800 431984 Pasuruan IDN 134019 432991 Pangkal Pinang IDN 124000 4331003 Pemalang IDN 103500 4341004 Klaten IDN 103300 4351007 Palangka Raya IDN 99693 4361020 Padang Sidempuan IDN 91200 4371045 Patna IND 917243 4381114 Panihati IND 275990 4391129 Patiala IND 238368 4401142 Panipat IND 215218 4411159 Parbhani IND 190255 4421231 Pali IND 136842 4431263 Pathankot IND 123930 4441265 Palghat (Palakkad) IND 123289 4451293 Pallavaram IND 111866 4461319 Tellicherry (Thalassery) IND 103579 4471339 Palayankottai IND 97662 4481345 Patan IND 96109 4491436 Marv Dasht IRN 103579 4501468 Palermo ITA 683794 4511478 Padova ITA 211391 4521484 Parma ITA 168717 453SELECT * FROM City 454WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) 455OR ((ID BETWEEN 900 AND 1500) AND 456(Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); 457ID Name Country Population 4581 Kabul AFG 1780000 4592 Qandahar AFG 237500 4603 Herat AFG 186800 4614 Mazar-e-Sharif AFG 127800 4627 Haag NLD 440900 46316 Haarlem NLD 148772 46425 Haarlemmermeer NLD 110722 46533 Willemstad ANT 2345 46634 Tirana ALB 270000 46755 Andorra la Vella AND 21189 46856 Luanda AGO 2022000 46957 Huambo AGO 163100 47058 Lobito AGO 130000 47159 Benguela AGO 128300 47260 Namibe AGO 118200 47361 South Hill AIA 961 47462 The Valley AIA 595 47564 Dubai ARE 669181 47665 Abu Dhabi ARE 398695 47766 Sharja ARE 320095 47867 al-Ayn ARE 225970 47968 Ajman ARE 114395 480129 Oranjestad ABW 29034 481191 Hamilton BMU 1200 482528 Hartlepool GBR 92000 483529 Halifax GBR 91069 484914 Sekondi-Takoradi GHA 103653 485943 Palembang IDN 1222764 486950 Padang IDN 534474 487983 Palu IDN 142800 488984 Pasuruan IDN 134019 489991 Pangkal Pinang IDN 124000 4901003 Pemalang IDN 103500 4911004 Klaten IDN 103300 4921007 Palangka Raya IDN 99693 4931020 Padang Sidempuan IDN 91200 4941045 Patna IND 917243 4951114 Panihati IND 275990 4961129 Patiala IND 238368 4971142 Panipat IND 215218 4981159 Parbhani IND 190255 4991231 Pali IND 136842 5001263 Pathankot IND 123930 5011265 Palghat (Palakkad) IND 123289 5021293 Pallavaram IND 111866 5031319 Tellicherry (Thalassery) IND 103579 5041339 Palayankottai IND 97662 5051345 Patan IND 96109 5061436 Marv Dasht IRN 103579 5071468 Palermo ITA 683794 5081478 Padova ITA 211391 5091484 Parma ITA 168717 510SELECT * FROM City USE INDEX () 511WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) 512OR ((ID BETWEEN 100 AND 200) AND 513(Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); 514ID Name Country Population 5151 Kabul AFG 1780000 5162 Qandahar AFG 237500 5173 Herat AFG 186800 5184 Mazar-e-Sharif AFG 127800 5197 Haag NLD 440900 52016 Haarlem NLD 148772 52125 Haarlemmermeer NLD 110722 52233 Willemstad ANT 2345 52334 Tirana ALB 270000 52455 Andorra la Vella AND 21189 52556 Luanda AGO 2022000 52657 Huambo AGO 163100 52758 Lobito AGO 130000 52859 Benguela AGO 128300 52960 Namibe AGO 118200 53061 South Hill AIA 961 53162 The Valley AIA 595 53264 Dubai ARE 669181 53365 Abu Dhabi ARE 398695 53466 Sharja ARE 320095 53567 al-Ayn ARE 225970 53668 Ajman ARE 114395 537100 Paraná ARG 207041 538129 Oranjestad ABW 29034 539167 Jamalpur BGD 103556 540168 Pabna BGD 103277 541189 Parakou BEN 103577 542191 Hamilton BMU 1200 543SELECT * FROM City 544WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) 545OR ((ID BETWEEN 100 AND 200) AND 546(Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); 547ID Name Country Population 5481 Kabul AFG 1780000 5492 Qandahar AFG 237500 5503 Herat AFG 186800 5514 Mazar-e-Sharif AFG 127800 5527 Haag NLD 440900 55316 Haarlem NLD 148772 55425 Haarlemmermeer NLD 110722 55533 Willemstad ANT 2345 55634 Tirana ALB 270000 55755 Andorra la Vella AND 21189 55856 Luanda AGO 2022000 55957 Huambo AGO 163100 56058 Lobito AGO 130000 56159 Benguela AGO 128300 56260 Namibe AGO 118200 56361 South Hill AIA 961 56462 The Valley AIA 595 56564 Dubai ARE 669181 56665 Abu Dhabi ARE 398695 56766 Sharja ARE 320095 56867 al-Ayn ARE 225970 56968 Ajman ARE 114395 570100 Paraná ARG 207041 571129 Oranjestad ABW 29034 572167 Jamalpur BGD 103556 573168 Pabna BGD 103277 574189 Parakou BEN 103577 575191 Hamilton BMU 1200 576EXPLAIN 577SELECT * FROM City WHERE Population > 101000 AND Population < 102000; 578id select_type table type possible_keys key key_len ref rows Extra 5791 SIMPLE City range Population Population 4 NULL 39 Using index condition 580EXPLAIN 581SELECT * FROM City WHERE Population > 101000 AND Population < 110000; 582id select_type table type possible_keys key key_len ref rows Extra 5831 SIMPLE City range Population Population 4 NULL 328 Using index condition 584EXPLAIN 585SELECT * FROM City WHERE Country < 'C'; 586id select_type table type possible_keys key key_len ref rows Extra 5871 SIMPLE City range Country Country 3 NULL 435 Using index condition 588EXPLAIN 589SELECT * FROM City WHERE Country < 'AGO'; 590id select_type table type possible_keys key key_len ref rows Extra 5911 SIMPLE City range Country Country 3 NULL 5 Using index condition 592EXPLAIN 593SELECT * FROM City WHERE Name BETWEEN 'P' AND 'S'; 594id select_type table type possible_keys key key_len ref rows Extra 5951 SIMPLE City range Name Name 35 NULL 221 Using index condition 596EXPLAIN 597SELECT * FROM City WHERE Name BETWEEN 'P' AND 'Pb'; 598id select_type table type possible_keys key key_len ref rows Extra 5991 SIMPLE City range Name Name 35 NULL 39 Using index condition 600EXPLAIN 601SELECT * FROM City WHERE ID BETWEEN 3400 AND 3800; 602id select_type table type possible_keys key key_len ref rows Extra 6031 SIMPLE City range PRIMARY PRIMARY 4 NULL 401 Using index condition 604EXPLAIN 605SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800; 606id select_type table type possible_keys key key_len ref rows Extra 6071 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition 608EXPLAIN 609SELECT * FROM City WHERE Name LIKE 'P%'; 610id select_type table type possible_keys key key_len ref rows Extra 6111 SIMPLE City range Name Name 35 NULL 135 Using index condition 612EXPLAIN 613SELECT * FROM City 614WHERE ((Population > 101000 AND Population < 102000) AND 615(Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR 616((ID BETWEEN 3400 AND 3800) AND 617(Country < 'AGO' OR Name LIKE 'Pa%')); 618id select_type table type possible_keys key key_len ref rows Extra 6191 SIMPLE City index_merge PRIMARY,Population,Country,Name Country,Name,Population 3,35,4 NULL 83 Using sort_union(Country,Name,Population); Using where 620EXPLAIN 621SELECT * FROM City 622WHERE ((Population > 101000 AND Population < 110000) AND 623(Country < 'AGO' OR Name BETWEEN 'P' AND 'Pb')) OR 624((ID BETWEEN 3790 AND 3800) AND 625(Country < 'C' OR Name LIKE 'P%')); 626id select_type table type possible_keys key key_len ref rows Extra 6271 SIMPLE City index_merge PRIMARY,Population,Country,Name Country,Name,PRIMARY 3,35,4 NULL 55 Using sort_union(Country,Name,PRIMARY); Using where 628SELECT * FROM City USE INDEX () 629WHERE ((Population > 101000 AND Population < 102000) AND 630(Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR 631((ID BETWEEN 3400 AND 3800) AND 632(Country < 'AGO' OR Name LIKE 'Pa%')); 633ID Name Country Population 634169 Naogaon BGD 101266 635205 Francistown BWA 101805 636417 Itaituba BRA 101320 637418 Araras BRA 101046 638751 Potchefstroom ZAF 101817 6392909 Puno PER 101578 6403463 Pavlograd UKR 127000 641SELECT * FROM City 642WHERE ((Population > 101000 AND Population < 102000) AND 643(Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR 644((ID BETWEEN 3400 AND 3800) AND 645(Country < 'AGO' OR Name LIKE 'Pa%')); 646ID Name Country Population 647169 Naogaon BGD 101266 648205 Francistown BWA 101805 649417 Itaituba BRA 101320 650418 Araras BRA 101046 651751 Potchefstroom ZAF 101817 6522909 Puno PER 101578 6533463 Pavlograd UKR 127000 654SELECT * FROM City USE INDEX () 655WHERE ((Population > 101000 AND Population < 110000) AND 656(Country < 'AGO' OR Name BETWEEN 'P' AND 'Pb')) OR 657((ID BETWEEN 3790 AND 3800) AND 658(Country < 'C' OR Name LIKE 'P%')); 659ID Name Country Population 660168 Pabna BGD 103277 661189 Parakou BEN 103577 662750 Paarl ZAF 105768 6632865 Pak Pattan PAK 107800 6643797 Philadelphia USA 1517550 6653798 Phoenix USA 1321045 666SELECT * FROM City 667WHERE ((Population > 101000 AND Population < 110000) AND 668(Country < 'AGO' OR Name BETWEEN 'P' AND 'Pb')) OR 669((ID BETWEEN 3790 AND 3800) AND 670(Country < 'C' OR Name LIKE 'P%')); 671ID Name Country Population 672168 Pabna BGD 103277 673189 Parakou BEN 103577 674750 Paarl ZAF 105768 6752865 Pak Pattan PAK 107800 6763797 Philadelphia USA 1517550 6773798 Phoenix USA 1321045 678CREATE INDEX CountryPopulation ON City(Country,Population); 679EXPLAIN 680SELECT * FROM City WHERE Name LIKE 'Pas%'; 681id select_type table type possible_keys key key_len ref rows Extra 6821 SIMPLE City range Name Name 35 NULL 4 Using index condition 683EXPLAIN 684SELECT * FROM City WHERE Name LIKE 'P%'; 685id select_type table type possible_keys key key_len ref rows Extra 6861 SIMPLE City range Name Name 35 NULL 135 Using index condition 687EXPLAIN 688SELECT * FROM City WHERE (Population > 101000 AND Population < 103000); 689id select_type table type possible_keys key key_len ref rows Extra 6901 SIMPLE City range Population Population 4 NULL 81 Using index condition 691EXPLAIN 692SELECT * FROM City WHERE Country='USA'; 693id select_type table type possible_keys key key_len ref rows Extra 6941 SIMPLE City ref Country,CountryPopulation Country 3 const 267 Using index condition 695EXPLAIN 696SELECT * FROM City WHERE Country='FIN'; 697id select_type table type possible_keys key key_len ref rows Extra 6981 SIMPLE City ref Country,CountryPopulation Country 3 const 7 Using index condition 699EXPLAIN 700SELECT * FROM City 701WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') 702AND Country='USA'; 703id select_type table type possible_keys key key_len ref rows Extra 7041 SIMPLE City index_merge Population,Country,Name,CountryPopulation CountryPopulation,Name 7,35 NULL 14 Using sort_union(CountryPopulation,Name); Using where 705EXPLAIN 706SELECT * FROM City 707WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') 708AND Country='EST'; 709id select_type table type possible_keys key key_len ref rows Extra 7101 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 2 Using index condition; Using where 711SELECT * FROM City 712WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') 713AND Country='USA'; 714ID Name Country Population 7153943 Pasadena USA 141674 7163953 Pasadena USA 133936 7174023 Gary USA 102746 7184024 Berkeley USA 102743 7194025 Santa Clara USA 102361 7204026 Green Bay USA 102313 7214027 Cape Coral USA 102286 7224028 Arvada USA 102153 7234029 Pueblo USA 102121 7244030 Sandy USA 101853 7254031 Athens-Clarke County USA 101489 7264032 Cambridge USA 101355 727SELECT * FROM City USE INDEX () 728WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') 729AND Country='USA'; 730ID Name Country Population 7313943 Pasadena USA 141674 7323953 Pasadena USA 133936 7334023 Gary USA 102746 7344024 Berkeley USA 102743 7354025 Santa Clara USA 102361 7364026 Green Bay USA 102313 7374027 Cape Coral USA 102286 7384028 Arvada USA 102153 7394029 Pueblo USA 102121 7404030 Sandy USA 101853 7414031 Athens-Clarke County USA 101489 7424032 Cambridge USA 101355 743SELECT * FROM City 744WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') 745AND Country='FIN'; 746ID Name Country Population 747SELECT * FROM City USE INDEX () 748WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') 749AND Country='FIN'; 750ID Name Country Population 751CREATE INDEX CountryName ON City(Country,Name); 752EXPLAIN 753SELECT * FROM City WHERE Country='USA'; 754id select_type table type possible_keys key key_len ref rows Extra 7551 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 267 Using index condition 756EXPLAIN 757SELECT * FROM City WHERE Country='FIN'; 758id select_type table type possible_keys key key_len ref rows Extra 7591 SIMPLE City ref Country,CountryPopulation,CountryName CountryName 3 const 5 Using index condition 760EXPLAIN 761SELECT * FROM City WHERE Country='BRA'; 762id select_type table type possible_keys key key_len ref rows Extra 7631 SIMPLE City ref Country,CountryPopulation,CountryName CountryName 3 const 221 Using index condition 764EXPLAIN 765SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800; 766id select_type table type possible_keys key key_len ref rows Extra 7671 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition 768EXPLAIN 769SELECT * FROM City WHERE ID BETWEEN 4025 AND 4035; 770id select_type table type possible_keys key key_len ref rows Extra 7711 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition 772EXPLAIN 773SELECT * FROM City WHERE ID BETWEEN 4028 AND 4032; 774id select_type table type possible_keys key key_len ref rows Extra 7751 SIMPLE City range PRIMARY PRIMARY 4 NULL 5 Using index condition 776EXPLAIN 777SELECT * FROM City WHERE ID BETWEEN 3500 AND 3800; 778id select_type table type possible_keys key key_len ref rows Extra 7791 SIMPLE City range PRIMARY PRIMARY 4 NULL 301 Using index condition 780EXPLAIN 781SELECT * FROM City WHERE ID BETWEEN 4000 AND 4300; 782id select_type table type possible_keys key key_len ref rows Extra 7831 SIMPLE City range PRIMARY PRIMARY 4 NULL 80 Using index condition 784EXPLAIN 785SELECT * FROM City WHERE ID BETWEEN 250 and 260 ; 786id select_type table type possible_keys key key_len ref rows Extra 7871 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition 788EXPLAIN 789SELECT * FROM City WHERE (Population > 101000 AND Population < 102000); 790id select_type table type possible_keys key key_len ref rows Extra 7911 SIMPLE City range Population Population 4 NULL 39 Using index condition 792EXPLAIN 793SELECT * FROM City WHERE (Population > 101000 AND Population < 103000); 794id select_type table type possible_keys key key_len ref rows Extra 7951 SIMPLE City range Population Population 4 NULL 81 Using index condition 796EXPLAIN 797SELECT * FROM City WHERE Name LIKE 'Pa%'; 798id select_type table type possible_keys key key_len ref rows Extra 7991 SIMPLE City range Name Name 35 NULL 41 Using index condition 800set @tmp_range_vs_index_merge=@@optimizer_switch; 801set optimizer_switch='extended_keys=off'; 802EXPLAIN 803SELECT * FROM City 804WHERE ((Population > 101000 AND Population < 102000) OR 805ID BETWEEN 3790 AND 3800) AND Country='USA' 806 AND (Name LIKE 'Pa%' OR ID BETWEEN 4025 AND 4035); 807id select_type table type possible_keys key key_len ref rows Extra 8081 SIMPLE City index_merge PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryPopulation,PRIMARY 7,4 NULL 14 Using sort_union(CountryPopulation,PRIMARY); Using where 809EXPLAIN 810SELECT * FROM City 811WHERE ((Population > 101000 AND Population < 103000) OR 812ID BETWEEN 3790 AND 3800) AND Country='USA' 813 AND (Name LIKE 'Pa%' OR ID BETWEEN 4028 AND 4032); 814id select_type table type possible_keys key key_len ref rows Extra 8151 SIMPLE City index_merge PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName,PRIMARY 38,4 NULL 11 Using sort_union(CountryName,PRIMARY); Using where 816EXPLAIN 817SELECT * FROM City 818WHERE ((Population > 101000 AND Population < 110000) OR 819ID BETWEEN 3500 AND 3800) AND Country='FIN' 820 AND (Name BETWEEN 'P' AND 'T' OR ID BETWEEN 4000 AND 4300); 821id select_type table type possible_keys key key_len ref rows Extra 8221 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 3 const 5 Using index condition; Using where 823SELECT * FROM City USE INDEX () 824WHERE ((Population > 101000 AND Population < 102000) OR 825ID BETWEEN 3790 AND 3800) AND Country='USA' 826 AND (Name LIKE 'Pa%' OR ID BETWEEN 4025 AND 4035); 827ID Name Country Population 8284030 Sandy USA 101853 8294031 Athens-Clarke County USA 101489 8304032 Cambridge USA 101355 831SELECT * FROM City 832WHERE ((Population > 101000 AND Population < 102000) OR 833ID BETWEEN 3790 AND 3800) AND Country='USA' 834 AND (Name LIKE 'Pa%' OR ID BETWEEN 4025 AND 4035); 835ID Name Country Population 8364030 Sandy USA 101853 8374031 Athens-Clarke County USA 101489 8384032 Cambridge USA 101355 839SELECT * FROM City USE INDEX () 840WHERE ((Population > 101000 AND Population < 102000) OR 841ID BETWEEN 3790 AND 3800) AND Country='USA' 842 AND (Name LIKE 'Pa%' OR ID BETWEEN 4028 AND 4032); 843ID Name Country Population 8444030 Sandy USA 101853 8454031 Athens-Clarke County USA 101489 8464032 Cambridge USA 101355 847SELECT * FROM City 848WHERE ((Population > 101000 AND Population < 102000) OR 849ID BETWEEN 3790 AND 3800) AND Country='USA' 850 AND (Name LIKE 'Pa%' OR ID BETWEEN 4028 AND 4032); 851ID Name Country Population 8524030 Sandy USA 101853 8534031 Athens-Clarke County USA 101489 8544032 Cambridge USA 101355 855SELECT * FROM City USE INDEX () 856WHERE ((Population > 101000 AND Population < 102000) OR 857ID BETWEEN 3790 AND 3800) AND Country='FIN' 858 AND (Name LIKE 'Pa%' OR ID BETWEEN 4025 AND 4035); 859ID Name Country Population 860SELECT * FROM City 861WHERE ((Population > 101000 AND Population < 102000) OR 862ID BETWEEN 3790 AND 3800) AND Country='FIN' 863 AND (Name LIKE 'Pa%' OR ID BETWEEN 4025 AND 4035); 864ID Name Country Population 865EXPLAIN 866SELECT * FROM City 867WHERE ((Population > 101000 and Population < 102000) OR 868ID BETWEEN 3790 AND 3800) AND Country='USA' 869 OR (Name LIKE 'Pa%' OR ID BETWEEN 250 AND 260) AND Country='BRA'; 870id select_type table type possible_keys key key_len ref rows Extra 8711 SIMPLE City index_merge PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryPopulation,PRIMARY,CountryName 7,4,38 NULL 35 Using sort_union(CountryPopulation,PRIMARY,CountryName); Using where 872SELECT * FROM City USE INDEX () 873WHERE ((Population > 101000 and Population < 102000) OR 874ID BETWEEN 3790 AND 3800) AND Country='USA' 875 OR (Name LIKE 'Pa%' OR ID BETWEEN 250 AND 260) AND Country='BRA'; 876ID Name Country Population 877250 Mauá BRA 375055 878251 Carapicuíba BRA 357552 879252 Olinda BRA 354732 880253 Campina Grande BRA 352497 881254 São José do Rio Preto BRA 351944 882255 Caxias do Sul BRA 349581 883256 Moji das Cruzes BRA 339194 884257 Diadema BRA 335078 885258 Aparecida de Goiânia BRA 324662 886259 Piracicaba BRA 319104 887260 Cariacica BRA 319033 888285 Paulista BRA 248473 889339 Passo Fundo BRA 166343 890364 Parnaíba BRA 129756 891372 Paranaguá BRA 126076 892379 Palmas BRA 121919 893386 Patos de Minas BRA 119262 894424 Passos BRA 98570 895430 Paulo Afonso BRA 97291 896435 Parnamirim BRA 96210 897448 Patos BRA 90519 898451 Palhoça BRA 89465 8993793 New York USA 8008278 9003794 Los Angeles USA 3694820 9013795 Chicago USA 2896016 9023796 Houston USA 1953631 9033797 Philadelphia USA 1517550 9043798 Phoenix USA 1321045 9053799 San Diego USA 1223400 9063800 Dallas USA 1188580 9074030 Sandy USA 101853 9084031 Athens-Clarke County USA 101489 9094032 Cambridge USA 101355 910SELECT * FROM City 911WHERE ((Population > 101000 and Population < 102000) OR 912ID BETWEEN 3790 AND 3800) AND Country='USA' 913 OR (Name LIKE 'Pa%' OR ID BETWEEN 250 AND 260) AND Country='BRA'; 914ID Name Country Population 915250 Mauá BRA 375055 916251 Carapicuíba BRA 357552 917252 Olinda BRA 354732 918253 Campina Grande BRA 352497 919254 São José do Rio Preto BRA 351944 920255 Caxias do Sul BRA 349581 921256 Moji das Cruzes BRA 339194 922257 Diadema BRA 335078 923258 Aparecida de Goiânia BRA 324662 924259 Piracicaba BRA 319104 925260 Cariacica BRA 319033 926285 Paulista BRA 248473 927339 Passo Fundo BRA 166343 928364 Parnaíba BRA 129756 929372 Paranaguá BRA 126076 930379 Palmas BRA 121919 931386 Patos de Minas BRA 119262 932424 Passos BRA 98570 933430 Paulo Afonso BRA 97291 934435 Parnamirim BRA 96210 935448 Patos BRA 90519 936451 Palhoça BRA 89465 9373793 New York USA 8008278 9383794 Los Angeles USA 3694820 9393795 Chicago USA 2896016 9403796 Houston USA 1953631 9413797 Philadelphia USA 1517550 9423798 Phoenix USA 1321045 9433799 San Diego USA 1223400 9443800 Dallas USA 1188580 9454030 Sandy USA 101853 9464031 Athens-Clarke County USA 101489 9474032 Cambridge USA 101355 948EXPLAIN 949SELECT * FROM City 950WHERE ((Population > 101000 AND Population < 11000) OR 951ID BETWEEN 3500 AND 3800) AND Country='USA' 952 AND (Name LIKE 'P%' OR ID BETWEEN 4000 AND 4300); 953id select_type table type possible_keys key key_len ref rows Extra 9541 SIMPLE City range|filter PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName|PRIMARY 38|4 NULL 23 (7%) Using index condition; Using where; Using rowid filter 955EXPLAIN 956SELECT * FROM City 957WHERE ((Population > 101000 AND Population < 11000) OR 958ID BETWEEN 3500 AND 3800) AND Country='USA' 959 AND (Name LIKE 'Pho%' OR ID BETWEEN 4000 AND 4300); 960id select_type table type possible_keys key key_len ref rows Extra 9611 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName Name 35 NULL 1 Using where 962SELECT * FROM City USE INDEX () 963WHERE ((Population > 101000 AND Population < 11000) OR 964ID BETWEEN 3500 AND 3800) AND Country='USA' 965 AND (Name LIKE 'P%' OR ID BETWEEN 4000 AND 4300); 966ID Name Country Population 9673797 Philadelphia USA 1517550 9683798 Phoenix USA 1321045 969SELECT * FROM City 970WHERE ((Population > 101000 AND Population < 11000) OR 971ID BETWEEN 3500 AND 3800) AND Country='USA' 972 AND (Name LIKE 'P%' OR ID BETWEEN 4000 AND 4300); 973ID Name Country Population 9743797 Philadelphia USA 1517550 9753798 Phoenix USA 1321045 976SELECT * FROM City USE INDEX () 977WHERE ((Population > 101000 AND Population < 11000) OR 978ID BETWEEN 3500 AND 3800) AND Country='USA' 979 AND (Name LIKE 'Pho%' OR ID BETWEEN 4000 AND 4300); 980ID Name Country Population 9813798 Phoenix USA 1321045 982SELECT * FROM City 983WHERE ((Population > 101000 AND Population < 11000) OR 984ID BETWEEN 3500 AND 3800) AND Country='USA' 985 AND (Name LIKE 'Pho%' OR ID BETWEEN 4000 AND 4300); 986ID Name Country Population 9873798 Phoenix USA 1321045 988DROP INDEX Population ON City; 989DROP INDEX Name ON City; 990set optimizer_switch=@tmp_range_vs_index_merge; 991EXPLAIN 992SELECT * FROM City 993WHERE Country='USA' AND Population BETWEEN 101000 AND 102000 OR 994Country='USA' AND Name LIKE 'Pa%'; 995id select_type table type possible_keys key key_len ref rows Extra 9961 SIMPLE City index_merge Country,CountryPopulation,CountryName CountryPopulation,CountryName 7,38 NULL 10 Using sort_union(CountryPopulation,CountryName); Using where 997SELECT * FROM City USE INDEX() 998WHERE Country='USA' AND Population BETWEEN 101000 AND 102000 OR 999Country='USA' AND Name LIKE 'Pa%'; 1000ID Name Country Population 10013932 Paterson USA 149222 10023943 Pasadena USA 141674 10033953 Pasadena USA 133936 10043967 Paradise USA 124682 10053986 Palmdale USA 116670 10064030 Sandy USA 101853 10074031 Athens-Clarke County USA 101489 10084032 Cambridge USA 101355 1009SELECT * FROM City 1010WHERE Country='USA' AND Population BETWEEN 101000 AND 102000 OR 1011Country='USA' AND Name LIKE 'Pa%'; 1012ID Name Country Population 10133932 Paterson USA 149222 10143943 Pasadena USA 141674 10153953 Pasadena USA 133936 10163967 Paradise USA 124682 10173986 Palmdale USA 116670 10184030 Sandy USA 101853 10194031 Athens-Clarke County USA 101489 10204032 Cambridge USA 101355 1021EXPLAIN 1022SELECT * FROM City 1023WHERE Country='USA' AND 1024(Population BETWEEN 101000 AND 102000 OR Name LIKE 'Pa%'); 1025id select_type table type possible_keys key key_len ref rows Extra 10261 SIMPLE City index_merge Country,CountryPopulation,CountryName CountryPopulation,CountryName 7,38 NULL 10 Using sort_union(CountryPopulation,CountryName); Using where 1027SELECT * FROM City 1028WHERE Country='USA' AND 1029(Population BETWEEN 101000 AND 102000 OR Name LIKE 'Pa%'); 1030ID Name Country Population 10313932 Paterson USA 149222 10323943 Pasadena USA 141674 10333953 Pasadena USA 133936 10343967 Paradise USA 124682 10353986 Palmdale USA 116670 10364030 Sandy USA 101853 10374031 Athens-Clarke County USA 101489 10384032 Cambridge USA 101355 1039SELECT * FROM City 1040WHERE Country='USA' AND 1041(Population BETWEEN 101000 AND 102000 OR Name LIKE 'Pa%'); 1042ID Name Country Population 10433932 Paterson USA 149222 10443943 Pasadena USA 141674 10453953 Pasadena USA 133936 10463967 Paradise USA 124682 10473986 Palmdale USA 116670 10484030 Sandy USA 101853 10494031 Athens-Clarke County USA 101489 10504032 Cambridge USA 101355 1051set @save_optimizer_switch=@@optimizer_switch; 1052CREATE INDEX CityName on City(Name); 1053EXPLAIN SELECT Name, Country, Population FROM City WHERE 1054(Name='Manila' AND Country='PHL') OR 1055(Name='Addis Abeba' AND Country='ETH') OR 1056(Name='Jakarta' AND Country='IDN') OR 1057(Name='Bangalore' AND Country='IND') OR 1058(Name='Teheran' AND Country='IRN') OR 1059(Name='Roma' AND Country='ITA') OR 1060(Name='Delhi' AND Country='IND') OR 1061(Name='Venezia' AND Country='ITA') OR 1062(Name='Tokyo' AND Country='JPN') OR 1063(Name='Toronto' AND Country='CAN') OR 1064(Name='Peking' AND Country='CHN') OR 1065(Name='Lagos' AND Country='NGA') OR 1066(Name='Tijuana' AND Country='MEX') OR 1067(Name='Rabat' AND Country='MAR') OR 1068(Name='Seoul' AND Country='KOR') OR 1069(Name='Vancouver' AND Country='CAN') OR 1070(Name='Kaunas' AND Country='LTU') OR 1071(Name='Paris' AND Country='FRA') OR 1072(Name='Dakar' AND Country='SEN') OR 1073(Name='Basel' AND Country='CHE') OR 1074(Name='Praha' AND Country='CZE') OR 1075(Name='Ankara' AND Country='TUR') OR 1076(Name='Dresden' AND Country='DEU') OR 1077(Name='Lugansk' AND Country='UKR') OR 1078(Name='Caracas' AND Country='VEN') OR 1079(Name='Samara' AND Country='RUS') OR 1080(Name='Seattle' AND Country='USA'); 1081id select_type table type possible_keys key key_len ref rows Extra 10821 SIMPLE City index_merge Country,CountryPopulation,CountryName,CityName CountryName,CityName 38,35 NULL 28 Using sort_union(CountryName,CityName); Using where 1083SELECT Name, Country, Population FROM City WHERE 1084(Name='Manila' AND Country='PHL') OR 1085(Name='Addis Abeba' AND Country='ETH') OR 1086(Name='Jakarta' AND Country='IDN') OR 1087(Name='Bangalore' AND Country='IND') OR 1088(Name='Teheran' AND Country='IRN') OR 1089(Name='Roma' AND Country='ITA') OR 1090(Name='Delhi' AND Country='IND') OR 1091(Name='Venezia' AND Country='ITA') OR 1092(Name='Tokyo' AND Country='JPN') OR 1093(Name='Toronto' AND Country='CAN') OR 1094(Name='Peking' AND Country='CHN') OR 1095(Name='Lagos' AND Country='NGA') OR 1096(Name='Tijuana' AND Country='MEX') OR 1097(Name='Rabat' AND Country='MAR') OR 1098(Name='Seoul' AND Country='KOR') OR 1099(Name='Vancouver' AND Country='CAN') OR 1100(Name='Kaunas' AND Country='LTU') OR 1101(Name='Paris' AND Country='FRA') OR 1102(Name='Dakar' AND Country='SEN') OR 1103(Name='Basel' AND Country='CHE') OR 1104(Name='Praha' AND Country='CZE') OR 1105(Name='Ankara' AND Country='TUR') OR 1106(Name='Dresden' AND Country='DEU') OR 1107(Name='Lugansk' AND Country='UKR') OR 1108(Name='Caracas' AND Country='VEN') OR 1109(Name='Samara' AND Country='RUS') OR 1110(Name='Seattle' AND Country='USA'); 1111Name Country Population 1112Addis Abeba ETH 2495000 1113Ankara TUR 3038159 1114Bangalore IND 2660088 1115Basel CHE 166700 1116Caracas VEN 1975294 1117Dakar SEN 785071 1118Delhi IND 7206704 1119Dresden DEU 476668 1120Jakarta IDN 9604900 1121Kaunas LTU 412639 1122Lagos NGA 1518000 1123Lugansk UKR 469000 1124Manila PHL 1581082 1125Paris FRA 2125246 1126Peking CHN 7472000 1127Praha CZE 1181126 1128Rabat MAR 623457 1129Roma ITA 2643581 1130Samara RUS 1156100 1131Seattle USA 563374 1132Seoul KOR 9981619 1133Teheran IRN 6758845 1134Tijuana MEX 1212232 1135Tokyo JPN 7980230 1136Toronto CAN 688275 1137Vancouver CAN 514008 1138Venezia ITA 277305 1139set optimizer_switch='index_merge=off'; 1140EXPLAIN SELECT Name, Country, Population FROM City WHERE 1141(Name='Manila' AND Country='PHL') OR 1142(Name='Addis Abeba' AND Country='ETH') OR 1143(Name='Jakarta' AND Country='IDN') OR 1144(Name='Bangalore' AND Country='IND') OR 1145(Name='Teheran' AND Country='IRN') OR 1146(Name='Roma' AND Country='ITA') OR 1147(Name='Delhi' AND Country='IND') OR 1148(Name='Venezia' AND Country='ITA') OR 1149(Name='Tokyo' AND Country='JPN') OR 1150(Name='Toronto' AND Country='CAN') OR 1151(Name='Peking' AND Country='CHN') OR 1152(Name='Lagos' AND Country='NGA') OR 1153(Name='Tijuana' AND Country='MEX') OR 1154(Name='Rabat' AND Country='MAR') OR 1155(Name='Seoul' AND Country='KOR') OR 1156(Name='Vancouver' AND Country='CAN') OR 1157(Name='Kaunas' AND Country='LTU') OR 1158(Name='Paris' AND Country='FRA') OR 1159(Name='Dakar' AND Country='SEN') OR 1160(Name='Basel' AND Country='CHE') OR 1161(Name='Praha' AND Country='CZE') OR 1162(Name='Ankara' AND Country='TUR') OR 1163(Name='Dresden' AND Country='DEU') OR 1164(Name='Lugansk' AND Country='UKR') OR 1165(Name='Caracas' AND Country='VEN') OR 1166(Name='Samara' AND Country='RUS') OR 1167(Name='Seattle' AND Country='USA'); 1168id select_type table type possible_keys key key_len ref rows Extra 11691 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition 1170SELECT Name, Country, Population FROM City WHERE 1171(Name='Manila' AND Country='PHL') OR 1172(Name='Addis Abeba' AND Country='ETH') OR 1173(Name='Jakarta' AND Country='IDN') OR 1174(Name='Bangalore' AND Country='IND') OR 1175(Name='Teheran' AND Country='IRN') OR 1176(Name='Roma' AND Country='ITA') OR 1177(Name='Delhi' AND Country='IND') OR 1178(Name='Venezia' AND Country='ITA') OR 1179(Name='Tokyo' AND Country='JPN') OR 1180(Name='Toronto' AND Country='CAN') OR 1181(Name='Peking' AND Country='CHN') OR 1182(Name='Lagos' AND Country='NGA') OR 1183(Name='Tijuana' AND Country='MEX') OR 1184(Name='Rabat' AND Country='MAR') OR 1185(Name='Seoul' AND Country='KOR') OR 1186(Name='Vancouver' AND Country='CAN') OR 1187(Name='Kaunas' AND Country='LTU') OR 1188(Name='Paris' AND Country='FRA') OR 1189(Name='Dakar' AND Country='SEN') OR 1190(Name='Basel' AND Country='CHE') OR 1191(Name='Praha' AND Country='CZE') OR 1192(Name='Ankara' AND Country='TUR') OR 1193(Name='Dresden' AND Country='DEU') OR 1194(Name='Lugansk' AND Country='UKR') OR 1195(Name='Caracas' AND Country='VEN') OR 1196(Name='Samara' AND Country='RUS') OR 1197(Name='Seattle' AND Country='USA'); 1198Name Country Population 1199Addis Abeba ETH 2495000 1200Ankara TUR 3038159 1201Bangalore IND 2660088 1202Basel CHE 166700 1203Caracas VEN 1975294 1204Dakar SEN 785071 1205Delhi IND 7206704 1206Dresden DEU 476668 1207Jakarta IDN 9604900 1208Kaunas LTU 412639 1209Lagos NGA 1518000 1210Lugansk UKR 469000 1211Manila PHL 1581082 1212Paris FRA 2125246 1213Peking CHN 7472000 1214Praha CZE 1181126 1215Rabat MAR 623457 1216Roma ITA 2643581 1217Samara RUS 1156100 1218Seattle USA 563374 1219Seoul KOR 9981619 1220Teheran IRN 6758845 1221Tijuana MEX 1212232 1222Tokyo JPN 7980230 1223Toronto CAN 688275 1224Vancouver CAN 514008 1225Venezia ITA 277305 1226set optimizer_switch=@save_optimizer_switch; 1227# 1228# Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n 1229# (LP bug #637962) 1230# 1231DROP INDEX CountryPopulation ON City; 1232DROP INDEX CountryName ON City; 1233DROP INDEX CityName on City; 1234CREATE INDEX Name ON City(Name); 1235CREATE INDEX Population ON City(Population); 1236EXPLAIN 1237SELECT * FROM City 1238WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) 1239AND (Population >= 100000 AND Population < 120000); 1240id select_type table type possible_keys key key_len ref rows Extra 12411 SIMPLE City index_merge Country,Name,Population Name,Country 35,3 NULL # Using sort_union(Name,Country); Using where 1242FLUSH STATUS; 1243SELECT * FROM City 1244WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) 1245AND (Population >= 100000 AND Population < 120000); 1246ID Name Country Population 1247384 Cabo Frio BRA 119503 1248387 Camaragibe BRA 118968 1249403 Catanduva BRA 107761 1250412 Cachoeirinha BRA 103240 1251508 Watford GBR 113080 1252509 Ipswich GBR 114000 1253510 Slough GBR 112000 1254511 Exeter GBR 111000 1255512 Cheltenham GBR 106000 1256513 Gloucester GBR 107000 1257514 Saint Helens GBR 106293 1258515 Sutton Coldfield GBR 106001 1259516 York GBR 104425 1260517 Oldham GBR 103931 1261518 Basildon GBR 100924 1262519 Worthing GBR 100000 1263635 Mallawi EGY 119283 1264636 Bilbays EGY 113608 1265637 Mit Ghamr EGY 101801 1266638 al-Arish EGY 100447 1267701 Tarragona ESP 113016 1268702 Lleida (Lérida) ESP 112207 1269703 Jaén ESP 109247 1270704 Ourense (Orense) ESP 109120 1271705 Mataró ESP 104095 1272706 Algeciras ESP 103106 1273707 Marbella ESP 101144 1274759 Gonder ETH 112249 1275869 Cabuyao PHL 106630 1276870 Calapan PHL 105910 1277873 Cauayan PHL 103952 1278903 Serekunda GMB 102600 1279909 Sohumi GEO 111700 1280913 Tema GHA 109975 1281914 Sekondi-Takoradi GHA 103653 1282924 Villa Nueva GTM 101295 12831844 Cape Breton CAN 114733 12841847 Cambridge CAN 109186 12852406 Herakleion GRC 116178 12862407 Kallithea GRC 114233 12872408 Larisa GRC 113090 12882908 Cajamarca PER 108009 12893002 Besançon FRA 117733 12903003 Caen FRA 113987 12913004 Orléans FRA 113126 12923005 Mulhouse FRA 110359 12933006 Rouen FRA 106592 12943007 Boulogne-Billancourt FRA 106367 12953008 Perpignan FRA 105115 12963009 Nancy FRA 103605 12973411 Ceyhan TUR 102412 12983567 Carúpano VEN 119639 12993568 Catia La Mar VEN 117012 13003571 Calabozo VEN 107146 13013786 Cam Ranh VNM 114041 13023792 Tartu EST 101246 13034002 Carrollton USA 109576 13044027 Cape Coral USA 102286 13054032 Cambridge USA 101355 1306SHOW STATUS LIKE 'Handler_read_%'; 1307Variable_name Value 1308Handler_read_first 0 1309Handler_read_key 2 1310Handler_read_last 0 1311Handler_read_next 385 1312Handler_read_prev 0 1313Handler_read_retry 0 1314Handler_read_rnd 377 1315Handler_read_rnd_deleted 0 1316Handler_read_rnd_next 0 1317EXPLAIN 1318SELECT * FROM City 1319WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) 1320AND (Population >= 100000 AND Population < 120000) 1321ORDER BY Population LIMIT 5; 1322id select_type table type possible_keys key key_len ref rows Extra 13231 SIMPLE City range Country,Name,Population Population 4 NULL # Using where 1324FLUSH STATUS; 1325SELECT * FROM City 1326WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) 1327AND (Population >= 100000 AND Population < 120000) 1328ORDER BY Population LIMIT 5; 1329ID Name Country Population 1330519 Worthing GBR 100000 1331638 al-Arish EGY 100447 1332518 Basildon GBR 100924 1333707 Marbella ESP 101144 13343792 Tartu EST 101246 1335SHOW STATUS LIKE 'Handler_read_%'; 1336Variable_name Value 1337Handler_read_first 0 1338Handler_read_key 1 1339Handler_read_last 0 1340Handler_read_next 59 1341Handler_read_prev 0 1342Handler_read_retry 0 1343Handler_read_rnd 0 1344Handler_read_rnd_deleted 0 1345Handler_read_rnd_next 0 1346set @tmp_mdev585=@@optimizer_use_condition_selectivity; 1347set optimizer_use_condition_selectivity=1; 1348EXPLAIN 1349SELECT * FROM City 1350WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) 1351AND (Population >= 100000 AND Population < 120000) 1352ORDER BY Population LIMIT 5; 1353id select_type table type possible_keys key key_len ref rows Extra 13541 SIMPLE City range Country,Name,Population Population 4 NULL # Using where 1355FLUSH STATUS; 1356SELECT * FROM City 1357WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) 1358AND (Population >= 100000 AND Population < 120000) 1359ORDER BY Population LIMIT 5; 1360ID Name Country Population 1361519 Worthing GBR 100000 1362638 al-Arish EGY 100447 1363518 Basildon GBR 100924 1364707 Marbella ESP 101144 13653792 Tartu EST 101246 1366SHOW STATUS LIKE 'Handler_read_%'; 1367Variable_name Value 1368Handler_read_first 0 1369Handler_read_key 1 1370Handler_read_last 0 1371Handler_read_next 59 1372Handler_read_prev 0 1373Handler_read_retry 0 1374Handler_read_rnd 0 1375Handler_read_rnd_deleted 0 1376Handler_read_rnd_next 0 1377set optimizer_use_condition_selectivity=@tmp_mdev585; 1378set optimizer_switch='index_merge=off'; 1379EXPLAIN 1380SELECT * FROM City 1381WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) 1382AND (Population >= 100000 AND Population < 120000) 1383ORDER BY Population LIMIT 5; 1384id select_type table type possible_keys key key_len ref rows Extra 13851 SIMPLE City range Country,Name,Population Population 4 NULL # Using index condition; Using where 1386FLUSH STATUS; 1387SELECT * FROM City 1388WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) 1389AND (Population >= 100000 AND Population < 120000) 1390ORDER BY Population LIMIT 5; 1391ID Name Country Population 1392519 Worthing GBR 100000 1393638 al-Arish EGY 100447 1394518 Basildon GBR 100924 1395707 Marbella ESP 101144 13963792 Tartu EST 101246 1397SHOW STATUS LIKE 'Handler_read_%'; 1398Variable_name Value 1399Handler_read_first 0 1400Handler_read_key 1 1401Handler_read_last 0 1402Handler_read_next 59 1403Handler_read_prev 0 1404Handler_read_retry 0 1405Handler_read_rnd 0 1406Handler_read_rnd_deleted 0 1407Handler_read_rnd_next 0 1408set optimizer_switch=@save_optimizer_switch; 1409DROP DATABASE world; 1410use test; 1411CREATE TABLE t1 ( 1412id int(10) unsigned NOT NULL auto_increment, 1413account_id int(10) unsigned NOT NULL, 1414first_name varchar(50) default NULL, 1415middle_name varchar(50) default NULL, 1416last_name varchar(100) default NULL, 1417home_address_1 varchar(150) default NULL, 1418home_city varchar(75) default NULL, 1419home_state char(2) default NULL, 1420home_postal_code varchar(50) default NULL, 1421home_county varchar(75) default NULL, 1422home_country char(3) default NULL, 1423work_address_1 varchar(150) default NULL, 1424work_city varchar(75) default NULL, 1425work_state char(2) default NULL, 1426work_postal_code varchar(50) default NULL, 1427work_county varchar(75) default NULL, 1428work_country char(3) default NULL, 1429login varchar(50) NOT NULL, 1430PRIMARY KEY (id), 1431KEY login (login,account_id), 1432KEY account_id (account_id), 1433KEY user_home_country_indx (home_country), 1434KEY user_work_country_indx (work_country), 1435KEY user_home_state_indx (home_state), 1436KEY user_work_state_indx (work_state), 1437KEY user_home_city_indx (home_city), 1438KEY user_work_city_indx (work_city), 1439KEY user_first_name_indx (first_name), 1440KEY user_last_name_indx (last_name) 1441); 1442insert into t1(account_id, login, home_state, work_state) values 1443(1, 'pw', 'ia', 'ia'), (1, 'pw', 'ia', 'ia'), (1, 'pw', 'ia', 'ia'), 1444(1, 'pw', 'ia', 'ia'), (1, 'pw', 'ia', 'ia'), (1, 'pw', 'ia', 'ia'); 1445insert into t1(account_id, login, home_state, work_state) 1446select 1, 'pw', 'ak', 'ak' from t1; 1447insert into t1(account_id, login, home_state, work_state) 1448select 1, 'pw', 'ak', 'ak' from t1; 1449insert into t1(account_id, login, home_state, work_state) 1450select 1, 'pw', 'ak', 'ak' from t1; 1451insert into t1(account_id, login, home_state, work_state) 1452select 1, 'pw', 'ak', 'ak' from t1; 1453insert into t1(account_id, login, home_state, work_state) 1454select 1, 'pw', 'ak', 'ak' from t1; 1455insert into t1(account_id, login, home_state, work_state) 1456select 1, 'pw', 'ak', 'ak' from t1; 1457insert into t1(account_id, login, home_state, work_state) 1458select 1, 'pw', 'ak', 'ak' from t1; 1459insert into t1(account_id, login, home_state, work_state) 1460select 1, 'pw', 'ak', 'ak' from t1; 1461insert into t1(account_id, login, home_state, work_state) 1462select 1, 'pw', 'ak', 'ak' from t1; 1463analyze table t1; 1464Table Op Msg_type Msg_text 1465test.t1 analyze status Engine-independent statistics collected 1466test.t1 analyze status OK 1467select count(*) from t1 where account_id = 1; 1468count(*) 14693072 1470select * from t1 1471where (home_state = 'ia' or work_state='ia') and account_id = 1; 1472id account_id first_name middle_name last_name home_address_1 home_city home_state home_postal_code home_county home_country work_address_1 work_city work_state work_postal_code work_county work_country login 14731 1 NULL NULL NULL NULL NULL ia NULL NULL NULL NULL NULL ia NULL NULL NULL pw 14742 1 NULL NULL NULL NULL NULL ia NULL NULL NULL NULL NULL ia NULL NULL NULL pw 14753 1 NULL NULL NULL NULL NULL ia NULL NULL NULL NULL NULL ia NULL NULL NULL pw 14764 1 NULL NULL NULL NULL NULL ia NULL NULL NULL NULL NULL ia NULL NULL NULL pw 14775 1 NULL NULL NULL NULL NULL ia NULL NULL NULL NULL NULL ia NULL NULL NULL pw 14786 1 NULL NULL NULL NULL NULL ia NULL NULL NULL NULL NULL ia NULL NULL NULL pw 1479explain 1480select * from t1 1481where (home_state = 'ia' or work_state='ia') and account_id = 1; 1482id select_type table type possible_keys key key_len ref rows Extra 14831 SIMPLE t1 index_merge account_id,user_home_state_indx,user_work_state_indx user_home_state_indx,user_work_state_indx 3,3 NULL 6 Using union(user_home_state_indx,user_work_state_indx); Using where 1484drop table t1; 1485CREATE TABLE t1 ( 1486c1 int(11) NOT NULL auto_increment, 1487c2 decimal(10,0) default NULL, 1488c3 decimal(10,0) default NULL, 1489c4 decimal(10,0) default NULL, 1490c5 decimal(10,0) default NULL, 1491cp decimal(1,0) default NULL, 1492ce decimal(10,0) default NULL, 1493cdata char(20), 1494PRIMARY KEY (c1), 1495KEY k1 (c2,c3,cp,ce), 1496KEY k2 (c4,c5,cp,ce) 1497); 1498insert into t1 (c2, c3, c4, c5, cp) values(1,1,1,1,1); 1499insert into t1 (c2, c3, c4, c5, cp) values(2,1,1,1,4); 1500insert into t1 (c2, c3, c4, c5, cp) values(2,1,2,1,1); 1501insert into t1 (c2, c3, c4, c5, cp) values(2,1,3,1,4); 1502insert into t1 (c2, c3, c4, c5, cp) values(3,1,4,1,4); 1503insert into t1 (c2, c3, c4, c5, cp) 1504select c2, c3, c4, c5, cp from t1 where cp = 4; 1505insert into t1 (c2, c3, c4, c5, cp) 1506select c2, c3, c4, c5, cp from t1 where cp = 4; 1507insert into t1 (c2, c3, c4, c5, cp) 1508select c2, c3, c4, c5, cp from t1 where cp = 4; 1509insert into t1 (c2, c3, c4, c5, cp) 1510select c2, c3, c4, c5, cp from t1 where cp = 4; 1511insert into t1 (c2, c3, c4, c5, cp) 1512select c2, c3, c4, c5, cp from t1 where cp = 4; 1513insert into t1 (c2, c3, c4, c5, cp) 1514select c2, c3, c4, c5, cp from t1 where cp = 4; 1515insert into t1 (c2, c3, c4, c5, cp) 1516select c2, c3, c4, c5, cp from t1 where cp = 4; 1517insert into t1 (c2, c3, c4, c5, cp) 1518select c2, c3, c4, c5, cp from t1 where cp = 4; 1519insert into t1 (c2, c3, c4, c5, cp) 1520select c2, c3, c4, c5, cp from t1 where cp = 4; 1521insert into t1 (c2, c3, c4, c5, cp) 1522select c2, c3, c4, c5, cp from t1 where cp = 4; 1523insert into t1 (c2, c3, c4, c5, cp) 1524select c2, c3, c4, c5, cp from t1 where cp = 4; 1525analyze table t1; 1526Table Op Msg_type Msg_text 1527test.t1 analyze status Engine-independent statistics collected 1528test.t1 analyze status OK 1529explain 1530select * from t1 where (c2=1 and c3=1) or (c4=2 and c5=1); 1531id select_type table type possible_keys key key_len ref rows Extra 15321 SIMPLE t1 index_merge k1,k2 k1,k2 12,12 NULL 2 Using sort_union(k1,k2); Using where 1533explain 1534select * from t1 1535where (c2=1 and c3=1 and cp=1) or (c4=2 and c5=1 and cp=1); 1536id select_type table type possible_keys key key_len ref rows Extra 15371 SIMPLE t1 index_merge k1,k2 k1,k2 14,14 NULL 2 Using sort_union(k1,k2); Using where 1538explain 1539select * from t1 1540where ((c2=1 and c3=1) or (c4=2 and c5=1)) and cp=1; 1541id select_type table type possible_keys key key_len ref rows Extra 15421 SIMPLE t1 index_merge k1,k2 k1,k2 14,14 NULL 2 Using sort_union(k1,k2); Using where 1543select * from t1 1544where (c2=1 and c3=1 and cp=1) or (c4=2 and c5=1 and cp=1); 1545c1 c2 c3 c4 c5 cp ce cdata 15461 1 1 1 1 1 NULL NULL 15473 2 1 2 1 1 NULL NULL 1548select * from t1 1549where ((c2=1 and c3=1) or (c4=2 and c5=1)) and cp=1; 1550c1 c2 c3 c4 c5 cp ce cdata 15511 1 1 1 1 1 NULL NULL 15523 2 1 2 1 1 NULL NULL 1553drop table t1; 1554create table t1 ( 1555c1 int auto_increment primary key, 1556c2 char(20), 1557c3 char (20), 1558c4 int 1559); 1560alter table t1 add key k1 (c2); 1561alter table t1 add key k2 (c3); 1562alter table t1 add key k3 (c4); 1563insert into t1 values(null, 'a', 'b', 0); 1564insert into t1 values(null, 'c', 'b', 0); 1565insert into t1 values(null, 'a', 'd', 0); 1566insert into t1 values(null, 'ccc', 'qqq', 0); 1567insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a'; 1568insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a'; 1569insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a'; 1570insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a'; 1571insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a'; 1572insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a'; 1573insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a'; 1574insert into t1 (c2,c3,c4) select c2,c3,1 from t1 where c2 != 'a'; 1575insert into t1 (c2,c3,c4) select c2,c3,2 from t1 where c2 != 'a'; 1576insert into t1 (c2,c3,c4) select c2,c3,3 from t1 where c2 != 'a'; 1577insert into t1 (c2,c3,c4) select c2,c3,4 from t1 where c2 != 'a'; 1578analyze table t1; 1579Table Op Msg_type Msg_text 1580test.t1 analyze status Engine-independent statistics collected 1581test.t1 analyze status OK 1582select count(*) from t1 where (c2='e' OR c3='q'); 1583count(*) 15840 1585select count(*) from t1 where c4 != 0; 1586count(*) 15873840 1588explain 1589select distinct c1 from t1 where (c2='e' OR c3='q'); 1590id select_type table type possible_keys key key_len ref rows Extra 15911 SIMPLE t1 index_merge k1,k2 k1,k2 21,21 NULL 2 Using union(k1,k2); Using where 1592explain 1593select distinct c1 from t1 where (c4!= 0) AND (c2='e' OR c3='q'); 1594id select_type table type possible_keys key key_len ref rows Extra 15951 SIMPLE t1 index_merge k1,k2,k3 k1,k2 21,21 NULL 2 Using union(k1,k2); Using where 1596drop table t1; 1597create table t1 ( 1598id int unsigned auto_increment primary key, 1599c1 char(12), 1600c2 char(15), 1601c3 char(1) 1602); 1603insert into t1 (c3) values ('1'), ('2'); 1604insert into t1 (c3) select c3 from t1; 1605insert into t1 (c3) select c3 from t1; 1606insert into t1 (c3) select c3 from t1; 1607insert into t1 (c3) select c3 from t1; 1608insert into t1 (c3) select c3 from t1; 1609insert into t1 (c3) select c3 from t1; 1610insert into t1 (c3) select c3 from t1; 1611insert into t1 (c3) select c3 from t1; 1612insert into t1 (c3) select c3 from t1; 1613insert into t1 (c3) select c3 from t1; 1614insert into t1 (c3) select c3 from t1; 1615insert into t1 (c3) select c3 from t1; 1616update t1 set c1=lpad(id+1000, 12, ' '), c2=lpad(id+10000, 15, ' '); 1617alter table t1 add unique index (c1), add unique index (c2), add index (c3); 1618analyze table t1; 1619Table Op Msg_type Msg_text 1620test.t1 analyze status Engine-independent statistics collected 1621test.t1 analyze status Table is already up to date 1622explain 1623select * from t1 where (c1=' 100000' or c2=' 2000000'); 1624id select_type table type possible_keys key key_len ref rows Extra 16251 SIMPLE t1 index_merge c1,c2 c1,c2 13,16 NULL 2 Using union(c1,c2); Using where 1626explain 1627select * from t1 where (c1=' 100000' or c2=' 2000000') and c3='2'; 1628id select_type table type possible_keys key key_len ref rows Extra 16291 SIMPLE t1 index_merge c1,c2,c3 c1,c2 13,16 NULL 2 Using union(c1,c2); Using where 1630select * from t1 where (c1=' 100000' or c2=' 2000000'); 1631id c1 c2 c3 1632select * from t1 where (c1=' 100000' or c2=' 2000000') and c3='2'; 1633id c1 c2 c3 1634drop table t1; 1635CREATE TABLE t1 ( 1636a smallint DEFAULT NULL, 1637pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, 1638b varchar(10) DEFAULT NULL, 1639c varchar(64) DEFAULT NULL, 1640INDEX idx1 (a), 1641INDEX idx2 (b), 1642INDEX idx3 (c) 1643); 1644SELECT COUNT(*) FROM t1 IGNORE INDEX (idx2,idx3) 1645WHERE c = 'i' OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' ) OR 1646(pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ; 1647COUNT(*) 16485 1649SELECT COUNT(*) FROM t1 1650WHERE c = 'i' OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' ) OR 1651(pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ; 1652COUNT(*) 16535 1654EXPLAIN 1655SELECT COUNT(*) FROM t1 1656WHERE c = 'i' OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' ) OR 1657(pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ; 1658id select_type table type possible_keys key key_len ref rows Extra 16591 SIMPLE t1 index_merge PRIMARY,idx1,idx2,idx3 idx3,idx2,PRIMARY,idx1 67,13,4,3 NULL 9 Using sort_union(idx3,idx2,PRIMARY,idx1); Using where 1660DROP TABLE t1; 1661CREATE TABLE t1 ( 1662f1 int, f2 int, f3 int, f4 int, f5 int, 1663PRIMARY KEY (f4), KEY (f1), KEY (f2), KEY (f3) 1664) ; 1665INSERT INTO t1 VALUES (0,0,NULL,9,5), (0,0,1,9425,NULL); 1666SELECT f5 FROM t1 1667WHERE f2 != 1 OR f1 IS NULL OR f4 = 4 OR 1668f2 AND (f4 BETWEEN 6 AND 255 OR f3 IS NULL); 1669f5 16705 1671NULL 1672DROP TABLE t1; 1673CREATE TABLE t1 ( 1674f1 int, f2 int, f3 int, f4 int, 1675PRIMARY KEY (f1), KEY (f3), KEY (f4) 1676); 1677INSERT INTO t1 VALUES (1,0,0,0), (2,0,0,0), (3,0,0,0); 1678INSERT INTO t1 VALUES (9,0,2,6), (9930,0,0,NULL); 1679insert into t1 select seq,seq,seq,seq from seq_100_to_400; 1680analyze table t1; 1681Table Op Msg_type Msg_text 1682test.t1 analyze status Engine-independent statistics collected 1683test.t1 analyze status OK 1684SET SESSION optimizer_switch='index_merge_intersection=off'; 1685SET SESSION optimizer_switch='index_merge_sort_union=off'; 1686SET SESSION optimizer_switch='index_merge_union=off'; 1687EXPLAIN 1688SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) 1689WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 1690OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); 1691id select_type table type possible_keys key key_len ref rows Extra 16921 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 306 Using where 1693SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) 1694WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 1695OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); 1696f1 f2 f3 f4 16979 0 2 6 1698SET SESSION optimizer_switch='index_merge_union=on'; 1699EXPLAIN 1700SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) 1701WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 1702OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); 1703id select_type table type possible_keys key key_len ref rows Extra 17041 SIMPLE t1 index_merge PRIMARY,f3,f4 f3,PRIMARY,f3 5,4,5 NULL 3 Using union(f3,PRIMARY,f3); Using where 1705SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) 1706WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 1707OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); 1708f1 f2 f3 f4 17099 0 2 6 1710INSERT INTO t1 VALUES 1711(93,0,3,6), (9933,0,3,3), (94,0,4,6), (9934,0,4,4), 1712(95,0,5,6), (9935,0,5,5), (96,0,6,6), (9936,0,6,6), 1713(97,0,7,6), (9937,0,7,7), (98,0,8,6), (9938,0,8,8), 1714(99,0,9,6), (9939,0,9,9); 1715analyze table t1; 1716Table Op Msg_type Msg_text 1717test.t1 analyze status Engine-independent statistics collected 1718test.t1 analyze status OK 1719SET SESSION optimizer_switch='index_merge_union=off'; 1720EXPLAIN 1721SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) 1722WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 1723OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); 1724id select_type table type possible_keys key key_len ref rows Extra 17251 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 320 Using where 1726SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) 1727WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 1728OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); 1729f1 f2 f3 f4 17309 0 2 6 1731SET SESSION optimizer_switch='index_merge_union=on'; 1732EXPLAIN 1733SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) 1734WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 1735OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); 1736id select_type table type possible_keys key key_len ref rows Extra 17371 SIMPLE t1 index_merge PRIMARY,f3,f4 f3,PRIMARY,f3 5,4,5 NULL 3 Using union(f3,PRIMARY,f3); Using where 1738SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) 1739WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 1740OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); 1741f1 f2 f3 f4 17429 0 2 6 1743SET SESSION optimizer_switch=DEFAULT; 1744DROP TABLE t1; 1745CREATE TABLE t1 (f1 int) ; 1746INSERT INTO t1 VALUES (0), (0); 1747CREATE TABLE t2 (f1 int, f2 int, f3 int, f4 int, INDEX idx (f3,f2)) ; 1748INSERT INTO t2 VALUES (5,6,0,0), (0,4,0,0); 1749CREATE TABLE t3 (f1 int, f2 int, INDEX idx1 (f2,f1) , INDEX idx2 (f1)) ; 1750INSERT INTO t3 VALUES (6,0),( 4,0); 1751SELECT * FROM t1,t2,t3 1752WHERE (t2.f3 = 1 OR t3.f1=t2.f1) AND t3.f1 <> t2.f2 AND t3.f2 = t2.f4; 1753f1 f1 f2 f3 f4 f1 f2 1754DROP TABLE t1,t2,t3; 1755CREATE TABLE t1 ( 1756a int, b int, c int, d int, 1757PRIMARY KEY(b), INDEX idx1(d), INDEX idx2(d,b,c) 1758); 1759INSERT INTO t1 VALUES 1760(0,58,7,7),(0,63,2,0),(0,64,186,8),(0,65,1,-2), (0,71,190,-3), 1761(0,72,321,-7),(0,73,0,3),(0,74,5,25),(0,75,5,3); 1762ANALYZE TABLE t1; 1763Table Op Msg_type Msg_text 1764test.t1 analyze status Engine-independent statistics collected 1765test.t1 analyze status OK 1766SET SESSION optimizer_switch='index_merge_sort_union=off'; 1767EXPLAIN 1768SELECT * FROM t1 1769WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7; 1770id select_type table type possible_keys key key_len ref rows Extra 17711 SIMPLE t1 ALL PRIMARY,idx1,idx2 NULL NULL NULL 9 Using where 1772SELECT * FROM t1 1773WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7; 1774a b c d 17750 58 7 7 17760 64 186 8 17770 73 0 3 17780 74 5 25 17790 75 5 3 1780SET SESSION optimizer_switch='index_merge_sort_union=on'; 1781EXPLAIN 1782SELECT * FROM t1 1783WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7; 1784id select_type table type possible_keys key key_len ref rows Extra 17851 SIMPLE t1 ALL PRIMARY,idx1,idx2 NULL NULL NULL 9 Using where 1786SELECT * FROM t1 1787WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7; 1788a b c d 17890 58 7 7 17900 64 186 8 17910 73 0 3 17920 74 5 25 17930 75 5 3 1794SET SESSION optimizer_switch=DEFAULT; 1795DROP TABLE t1; 1796CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int, c int, INDEX idx(c,b)); 1797INSERT INTO t1 VALUES (19,1,NULL), (20,5,7); 1798EXPLAIN 1799SELECT * FROM t1 1800WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND 1801(t1.c=0 OR t1.a=500); 1802id select_type table type possible_keys key key_len ref rows Extra 18031 SIMPLE t1 range PRIMARY,idx PRIMARY 4 NULL 1 Using index condition; Using where 1804SELECT * FROM t1 1805WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND 1806(t1.c=0 OR t1.a=500); 1807a b c 1808DROP TABLE t1; 1809CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX idx(b)); 1810INSERT INTO t1 VALUES (167,9999), (168,10000); 1811EXPLAIN 1812SELECT * FROM t1 1813WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); 1814id select_type table type possible_keys key key_len ref rows Extra 18151 SIMPLE t1 ALL PRIMARY,idx NULL NULL NULL 2 Using where 1816SELECT * FROM t1 1817WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); 1818a b 1819167 9999 1820168 10000 1821DROP TABLE t1; 1822# 1823# MDEV-8603: Wrong result OR/AND condition over index fields 1824# 1825CREATE TABLE t1 ( 1826id INT NOT NULL, 1827state VARCHAR(64), 1828capital VARCHAR(64), 1829UNIQUE KEY (id), 1830KEY state (state,id), 1831KEY capital (capital, id) 1832); 1833INSERT INTO t1 VALUES 1834(1,'Arizona','Phoenix'), 1835(2,'Hawaii','Honolulu'), 1836(3,'Georgia','Atlanta'), 1837(4,'Florida','Tallahassee'), 1838(5,'Alaska','Juneau'), 1839(6,'Michigan','Lansing'), 1840(7,'Pennsylvania','Harrisburg'), 1841(8,'Virginia','Richmond') 1842; 1843ANALYZE TABLE t1; 1844Table Op Msg_type Msg_text 1845test.t1 analyze status Engine-independent statistics collected 1846test.t1 analyze status OK 1847EXPLAIN 1848SELECT * FROM t1 FORCE KEY (state,capital) 1849WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) 1850OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; 1851id select_type table type possible_keys key key_len ref rows Extra 18521 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where 1853SELECT * FROM t1 FORCE KEY (state,capital) 1854WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) 1855OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; 1856id state capital 18574 Florida Tallahassee 18583 Georgia Atlanta 18592 Hawaii Honolulu 18606 Michigan Lansing 18617 Pennsylvania Harrisburg 18628 Virginia Richmond 1863DROP TABLE t1; 1864# 1865# mdev-11574: do not build index merge of two indexes when 1866# one index is an infix of the other index 1867# 1868set names utf8; 1869CREATE DATABASE world; 1870use world; 1871CREATE TABLE Country ( 1872Code char(3) NOT NULL default '', 1873Name char(52) NOT NULL default '', 1874SurfaceArea float(10,2) NOT NULL default '0.00', 1875Population int(11) NOT NULL default '0', 1876Capital int(11) default NULL, 1877PRIMARY KEY (Code), 1878UNIQUE INDEX (Name) 1879); 1880CREATE TABLE City ( 1881ID int(11) NOT NULL auto_increment, 1882Name char(35) NOT NULL default '', 1883Country char(3) NOT NULL default '', 1884Population int(11) NOT NULL default '0', 1885PRIMARY KEY (ID), 1886INDEX (Population), 1887INDEX (Country) 1888); 1889CREATE TABLE CountryLanguage ( 1890Country char(3) NOT NULL default '', 1891Language char(30) NOT NULL default '', 1892Percentage float(3,1) NOT NULL default '0.0', 1893PRIMARY KEY (Country, Language), 1894INDEX (Percentage) 1895); 1896DROP INDEX Country ON City; 1897CREATE INDEX CountryName ON City(Country,Name); 1898CREATE INDEX Name ON City(Name); 1899select * from City 1900where 1901Country='FIN' AND Name IN ('Lahti','Imatra') OR 1902Country='RUS' AND Name IN ('St Petersburg', 'Moscow') OR 1903Country='SWE' AND Name IN ('Stockholm', 'Uppsala') OR 1904Country='DEU' AND Name IN ('Berlin', 'Bonn') OR 1905Country='BEL' AND Name IN ('Antwerpen', 'Gent') OR 1906Country='PRT' AND Name IN ('Braga', 'Porto') OR 1907Country='FRA' AND Name IN ('Paris', 'Marcel') OR 1908Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR 1909Country='NOR' AND Name IN ('Oslo', 'Bergen') OR 1910Country='ITA' AND Name IN ('Napoli', 'Venezia'); 1911ID Name Country Population 1912175 Antwerpen BEL 446525 1913176 Gent BEL 224180 19143068 Berlin DEU 3386667 19153087 Bonn DEU 301048 19163242 Lahti FIN 96921 19172974 Paris FRA 2125246 19181466 Napoli ITA 1002619 19191474 Venezia ITA 277305 19202808 Bergen NOR 230948 19212807 Oslo NOR 508726 19222928 Warszawa POL 1615369 19232931 Wroclaw POL 636765 19242918 Braga PRT 90535 19252915 Porto PRT 273060 19263580 Moscow RUS 8389200 19273581 St Petersburg RUS 4694000 19283048 Stockholm SWE 750348 19293051 Uppsala SWE 189569 1930explain select * from City 1931where 1932Country='FIN' AND Name IN ('Lahti','Imatra') OR 1933Country='RUS' AND Name IN ('St Petersburg', 'Moscow') OR 1934Country='SWE' AND Name IN ('Stockholm', 'Uppsala') OR 1935Country='DEU' AND Name IN ('Berlin', 'Bonn') OR 1936Country='BEL' AND Name IN ('Antwerpen', 'Gent') OR 1937Country='PRT' AND Name IN ('Braga', 'Porto') OR 1938Country='FRA' AND Name IN ('Paris', 'Marcel') OR 1939Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR 1940Country='NOR' AND Name IN ('Oslo', 'Bergen') OR 1941Country='ITA' AND Name IN ('Napoli', 'Venezia'); 1942id select_type table type possible_keys key key_len ref rows Extra 19431 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition 1944DROP DATABASE world; 1945set session optimizer_switch='index_merge_sort_intersection=default'; 1946