Create 'main' test directory and move 't' and 'r' there

1 files changed, 1043 insertions, 0 deletions

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